Incorrect grand totals (EXCEL, POWER BI)

[valers]

Hi,

We have 40+ measures created for our OLAP cube, some of them being more complex than others. Almost 70% of them give an incorrect grand total when used in EXCEL pivot tables/Power BI. We expect it to be a perfect sum of the rows. The main culprit is already known fact that the grand totals are the actual measure without any row context, which I understand and accept why it's like this.

It would be a huge, near to impossible, effort to tailor all the measures to give correct grand totals for most possible combinations of measures and dimensions.

What we need is that actually, a way to have grand totals sum up correctly without any excel hacks like having custom made grand totals. Also moving some of the measures in a calculated column it's not actually an option as we try to keep resource consumption to a minimum.

I have read a lot of similar posts here but none of them apply to my situation. I'm trying to fix this issue for multiple measures, not for only a specific one.  My only bet is to find a way of having the measures be precalculated in memory and then do a sum on them. Similar to SUMMARIZE but without the grouping. Something like: "Take this measure, calculated it, and then do a sum of its results".

I am curious if anyone has found a general solution to this grand total hell?

I can provide some specific examples if needed.

Thanks in advance!

[MFelix]

Hi @valers ,

On Power BI you can use calculation groups and create a measure that returns the correct value for the measure that you place on the visualization directly, because there is a DAX syntax that is SELECTEDMEASURE(), using this syntax the calculation you perform is made on the measure you place on the visualizations.

https://www.sqlbi.com/articles/introducing-calculation-groups/

In Excel I don't believe there is an option of having this calculation groups.

Calling out @marcorusso @AlbertoFerrari  do you know how to do this in excel is it to have a similar way of doing it has you have in Power BI and the calculation groups?