How to Aggregate Calculated Averages

[DataCatChan]

Hey All,

I have a large table of performance data that includes brands and sub-brands, which I use to calculate overall performance as an average. Forgive the anonymised data!

Example below, and relevant points are:

• % Diff is calculated as Total this year/ total previous year. This is just a dummy calculation, it doesn't actually show YOY.
• The sub-brands roll up to the brands, so the sum of sub-brands A1-A2 create the total for brand A.
• On the backend I have % Diff inserted as a calculated field added to the brand table.

Brand Name	Total Previous Year	Total This Year	% Diff
Sub-brand A1	7,485,594	7,392,238	98.75%
Sub-brand A2	262,547	260,149	99.09%
Brand A total	7,748,142	7,652,387	98.76%

My problem is that I can't correctly calculate the % Diff for Brand A in the report view. If I create a table of the averages then it takes the averages of sub-brands A1/A2 to give an overall average % diff as 98.92%, NOT the correct average % diff above. This is because it's averaging the two averages, instead of aggregating and averaging the underlying values.

So the calculation that's currently happening in PowerBI to get the % diff for Brand A total is:

(98.75% + 99.09%) / 2 = 98.92%

Which is wrong.

What should be happening is:

7,652,387 / 7,748,142= 98.76%

This happens automatically in Excel, if it helps. Any help appreciated, thanks!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.