How to Aggregate Calculated Averages

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.

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!