Sunday, February 11, 2024

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog