Power BI DAX实现Avg of AVG需求：先品牌均值再组均值

Got it, let's break down how to implement this "average of averages" logic in Power BI. The key here is to first compute the average percent change for each brand, then take the average of those brand-specific averages for each group—instead of just averaging all the raw percent change values in the group.

Step 1: Create a Measure for Brand-Level Average Percent Change #

First, we need a measure that calculates the average percent change per brand. This will aggregate the values for each individual brand:

Brand Avg % Change = 
AVERAGE('YourTableName'[PERCENT CHANGE])

(Make sure to replace 'YourTableName' with the actual name of your table in Power BI.)

This measure will return the average percent change for whichever brand is in the current context. For example, for BRAND J in GROUP 1, it would calculate (4.8% + 12.0%)/2 = 8.4%, which matches your example.

Step 2: Calculate the Group-Level Average of Brand Averages #

Next, we need a measure that takes those brand averages and computes their average for each group. We'll use SUMMARIZE to get a list of unique brands per group along with their averages, then average those values:

Group Avg of Brand Avgs = 
VAR BrandAvgTable = 
    SUMMARIZE(
        'YourTableName',
        'YourTableName'[BRAND],
        "@BrandAvg", [Brand Avg % Change]
    )
RETURN
    AVERAGEX(BrandAvgTable, [@BrandAvg])

Let's verify this with your GROUP 1 example:

• BRAND J's avg is 8.4%, BRAND K's is 4.8%, BRAND L's is 4.9%
• The average of these three is (8.4 + 4.8 + 4.9)/3 = 6.03%, which is exactly what you need—instead of the overall average of all 4 values (which would be 6.6%).

How to Use These Measures #

Once you've created both measures, you can add them to a visual (like a table) alongside the GROUP column. The Group Avg of Brand Avgs measure will automatically compute the correct average of averages for each group in your data.

A quick note: If your PERCENT CHANGE column is stored as a percentage (not a decimal), the DAX functions will handle it correctly—no need to convert, but just double-check that the data type is set properly in Power BI.

内容的提问来源于stack exchange，提问作者ETedford