Power BI DAX Avg of AVG

Question

I want the results to show, for example: AVG GROUP 1 takes the avg of the brand then avg's the group. So,

avg BRAND J = 8.4
avg BRAND K = 4.8
avg BRAND L = 4.9

AVG GROUP 1 would then = 6.03 NOT 6.6

This would then continue for all groups.

Thanks

DataTable

GROUP      BRAND       PERCENT CHANGE
GROUP 1    BRAND J     4.8%
GROUP 1    BRAND J     12.0%
GROUP 1    BRAND K     4.8%
GROUP 1    BRAND L     4.9%
GROUP 2    BRAND M     8.0%
GROUP 3    BRAND A     4.0%
GROUP 4    BRAND B     8.0%
GROUP 4    BRAND B     15.0%
GROUP 4    BRAND C     8.0%
GROUP 4    BRAND N     7.0%
GROUP 5    BRAND D     5.0%
GROUP 5    BRAND E     4.0%
GROUP 5    BRAND E     6.0%
GROUP 5    BRAND E     6.0%

user_3679147 · Answer

Create a measure:

Group Average =
AVERAGEX (
    SUMMARIZE (
        Data,
        Data[Group],
        Data[Brand],
        "Brand Average", AVERAGE ( Data[Percent Change] )
    ),
    [Brand Average]
)

where "Data" is your table name.

Result:

More detailed result:

How it works: First, we summarize your data by Group and Brand, and calculate average per each. Then, we use AVERAGEX to iterate the summarized table, and calculate the average of the averages.

Edit:

To filter out groups with 1 brand, modify the measure:

Group Average =
AVERAGEX(
        SUMMARIZE (
            Data,
            Data[Group],
            Data[Brand],
            "Brand Average", AVERAGE ( Data[Percent Change] ),
            "Brand Count", CALCULATE(COUNT(Data[BRAND]), ALLEXCEPT(Data, Data[GROUP])),
IF([Brand Count] > 1, [Brand Average])
)

You will get:

Power BI DAX Avg of AVG

1 Answers