How to Calculate the Average of Averages using DAX in Power BI

How to Calculate the Average of Main Category based on Averages of Sub Category using DAX in Power BI

Scenario:
We have sample Dataset as follows, with various Dimension levels like:
Region > Country > Product_Segment > Product

This sample dataset has the Quantity of Products sold value (Qnty_Sold) either by Online or Offline (Sale_Mode).

Based on this Dataset, first we need to Calculate the Ratio of Online Sales out of Total Sales by Product_Segment , Country and Region.

The Average Online Sales of Product_Segment is Average of Average Online Sales by Product
The Average Online Sales of Country is Average of Average Online Sales by Product_Segment

The Average Online Sales of Region is Average of Average Online Sales by Country

We can achieve this Scenario as discussed below.

Net Sales = SUM(ds_Sample[Qnty_Sold])

Total Product Sales = CALCULATE([Net Sales], ALL(ds_Sample[Sale_Mode]))

Online Sales = CALCULATE( [Net Sales],

            FILTER( VALUES(ds_Sample[Sale_Mode]), ds_Sample[Sale_Mode]="Online")

                )

% Online Sales = DIVIDE([Online Sales]+0, [Total Product Sales],0)

Avg Online Sales per Prod =

AVERAGEX( VALUES(ds_Sample[Prod_Name]),

          CALCULATE([% Online Sales], ALL(ds_Sample[Sale_Mode]))

        )

Notes:
It is required to add the [Online Sales]+0 , to show correct Average of Averages, by including the Blank() or Zero Values.

[Net Sales]: The amount of Product sales.

[Total Product Sales]: The total sales of Products across different [Sale_Mode] like Offline and Online.

[% Online Sales]: The Ratio of Online Sales out of Total Sales (Online + Offline).

[Avg Online Sales per Prod]: The Average Online sales per product

Note: 

[% Online Sales] = (291/664) = 0.438

[Avg Online Sales per Prod] = (0.315+0.710+0.281+0.506) /4 = 0.453

Since the Sales at the Prod_Name is the lowest level of granularity, the [% Online Sales] is same as [Avg Online Sales per Prod], when Prod_Name in the row context of the Visual.

---------------------------------------------------------------------
Now we will use the Average of [Avg Online Sales per Prod], to calculate the [Avg Online Sales per Prod_Segment], the above level of Prod_Name.

Avg Online Sales per Prod_Segment =

AVERAGEX( VALUES(ds_Sample[Prod_Segment]),

        CALCULATE([Avg Online Sales per Prod],

        ALL(ds_Sample[Sale_Mode]),ALL(ds_Sample[Prod_Name]))

        )

Note:
[Avg Online Sales per Prod_Segment] = (0.315+0.710+0.281+0.506) /4 = 0.453

---------------------------------------------------------------------

Similarly, we will use the Average of [Avg Online Sales per Prod_Segment] to calculate the [Avg Online Sales per Country], the above level of Prod_Segment.

Avg Online Sales per Country =

AVERAGEX( VALUES(ds_Sample[Country]),

           CALCULATE([Avg Online Sales per Prod_Segment],

                ALL(ds_Sample[Prod_Segment]),

                ALL(ds_Sample[Sale_Mode]), ALL(ds_Sample[Prod_Name])

            )

        )

Note:
[Avg Online Sales per Country] = (0.578+0.348+0.509)/3 = 0.478

---------------------------------------------------------------------

Similarly, we will use Average of [Avg Online Sales per Country] to calculate the [Avg Online Sales per Region], the above level of Country.

Avg Online Sales per Region =

AVERAGEX( VALUES(ds_Sample[Region]),

        CALCULATE([Avg Online Sales per Country],

            ALL(ds_Sample[Country]),ALL(ds_Sample[Prod_Segment]),

            ALL(ds_Sample[Sale_Mode]), ALL(ds_Sample[Prod_Name])

            )

        )

Note:
[Avg Online Sales per Region] = (0.478+0.487)/2= 0.482

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------