Percentage total in table using direct query to cube wrong

[mivar99]

I have a simple percentage calculation in cube that only shows correct total number in powerbi when i show elements without data on the attribute i dragged in

as you see the 981 mio doesnt change but the percentage does - makes no sense and probelm doesnt exist in excel

[Greg_Deckler]

Take a look at this article:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

[mivar99]

tx for the input but i dont think its an issue like this.

First of all i dont use dax for calculations - i connect to a live multimension cube (maybe powerbi uses dax behind the scenes)

I made my calculation inside the cube (since i didnt have any option to do local calculations on live cube connection)

and then last but not least. Excel shows it correctly out of the box ?

[Greg_Deckler]

The Total line is essentially using DAX behind the scenses to aggregate the column. I would suggest that if it is not aggregating it the way you want to create a measure to do it the way you want.

[mivar99]

and the problem is that i already thought i did and it works in excel - i would have to try to make a different type of calculation in mdx where its already correct.

at this point no clue how but ill look more into it.

[v-shex-msft]

HI @mivar99,

I think this issue may related to different filter logic/calculation on matrix and pivot table side. 

As smoupre said, sometimes you need to add filters and additional calculation formula in your measure to make sure the formulas get correct result on 'total' row.

For any detail about internal formula calculation process, you can try to contact to power bi dev team for further support.

Regards.

Xiaoxin Sheng

[mivar99]

hI !

As i see it its a DAX to MDX powerbi problem

Its a super simple straight forward percetntage calculation that works ok in excel that uses "normal" mdx to query the cube

I am sure other mdx front end tools would show the same

But i will see if the mdx formula can be changed to something that also works in powerbi

[Measures].[Disbursement (DKK)]/([Budget].[Indicatorstatus].[All],[Measures].[Disbursement (DKK)])

/m

[mivar99]

and the number shouldnt change when u say yes to show empty members ? if they are empty they shouldnt affect calculation ? Excel filters out the empty ones automatically and still shows correct percetnage calculation ?