Power BI matrix Column Total is correct but row total is wrong

[hitesh1607]

Hi -

I have one measure called XYZ

which is directly coming from the ABC table by measure XYZ= SUMX('ABC'[XYZ])
XYZ ( I am getting correct results for this measure) Note there are some blanks

name	Jan	Feb	Mar	row Total
a	10			10
b	5			5
Column Total	15			15

I have a second measure called RTY

which is directly coming from the DEF table by measure RTY = SUMX('DEF'[RTY])

This is correct too

name	Jan	Feb	Mar	row Total
a	10	10	10	30
b	10	20	10	40
Column Total	20	30	20	70

I am trying to calculate FINAL = If ( XYZ = 0, RTY, XYZ)

The final measure solution should be:

name	Jan	Feb	Mar	row Total
a	10	10	10	5 (i am getting this incorrect)
b	5	20	10	30(i am getting this incorrect)
Column Total	15	30	20	40((i am getting this incorrect)

[selimovd]

Hey @hitesh1607 ,

SUMX is an iterative function. If you just want the sum of a specific column just use the SUM function.

Try the following:

Sum XYZ = SUM( 'ABC'[XYZ] )

Sum RTY = SUM( 'DEF'[RTY] )

As another measure you can do your IF clause:

FINAL = IF ( [Sum XYZ] = 0, [Sum RTY], [Sum XYZ] )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[hitesh1607]

If I use the above code even then I am getting the wrong row subtotal. (Column subtotal is working fine in the matrix visual)

[V-lianl-msft]

Hi @hitesh1607 ,

Please try:

XYZ_ = SUM( 'ABC'[XYZ] )

RTY_ = SUM( 'DEF'[RTY] )

FINAL = 
VAR C_ = IF([XYZ_]=0,[RTY_],[XYZ_])
RETURN 
SUMX(VALUES('GHI'[MONTH]),C_)

If the problem persists,could you share the sample pbix with dummy data?

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.