Matrix - Can Primary Row Be It's Own Calculation From Sub-Rows?

[BBlue20025]

Hello. I am new to PowerBI, so I apologize if this is not explained correctly.

I have a matrix where there are Primary Rows each with Sub Rows similar to a Region and all the Stores that exist in that Region.

My data exists at the Store level and then I built a relationship to show which Store is in which Region.

When looking at the Store rows, I get the expected values for a % calculation. But the % for the Region is an average of the Store % values or whichever is selected.

So as an average I get the following:

If I go back to the original data and pivot the Store data by their Region and do the same calculation, I would not get the same value as the average.

Is there a way to get the Region's % to be its own calculation?

Thank you for your help.

[danextian]

Hi @BBlue20025 

You can show a different aggregation for the subtotals directly from the customize total options.

Or create a measure that iterates over a table or table expression

[danextian]

Are you trying to select Store A or Store B in the same visual and pass that value from that row to Region calculation? If so, that isn't possible.

[BBlue20025]

I'm not sure if this helps, but in PowerBI, Region A is -17% as the average of the Store's % and I wanted it to show -15% as this calculation.

 

[Juan-Power-bi]

The issue is that when Power BI rolls up to the Region level, it's averaging the store percentages instead of recalculating the % from the underlying data. You want it to recalculate from scratch at every level.
The fix is to make your measure always divide the totals directly rather than aggregating the child percentages. Something like:
daxStore % =
DIVIDE(
SUM('Sales'[YourNumerator]),
SUM('Sales'[YourDenominator])
)
When this measure runs at the Region level, Power BI automatically sums all the stores in that region before dividing

[BBlue20025]

I'm not too familiar with how to use dax yet.

In the original data, I have columns by Store and Month.

I created a column that was the average for the 3 oldest columns:

= Table.AddColumn(#"Reordered Columns", "JFM_AVG", each List.Average(List.RemoveNulls({[January], [February], [March]})))

Followed by a column to get the % comparison of the newest month compared to the AVG of the previous three months

= Table.AddColumn(#"Added Custom", "PERC", each (([April]/[JFM_AVG)-1))

And then a column to look up the Region based on the Store

= LOOKUPVALUE('Region'[Region],'Region'[Store],'Store'[Store])

How would I use the dax example? Where do I type it?

[Ashish_Mathur]

Hi,

Please share the download link of the PBI file.

[BBlue20025]

Unfortunately I cannot share the true data.

[Ashish_Mathur]

You can share anonymised data.

[BBlue20025]

I'm not sure how to modify it for that.

Does this image help explain? I currently get -17% in my Primary Row as the average of the Secondary Rows, but I want it to calculate as shown for -15%.