Measure is not summarizing in matrix visual

[ilyav]

Hi Everyone,

I have a measure that is not summarizing for some reason and I would love your help on this as I've not been able to find a solution in the forums.

Here is a snapshot of the data;

The use case is that the column 'Variable VA' needs to accept 3 user inputs based on the row context (Memories, RBOM,SOC) so I have created 3 numeric parameters (range from 15-25) and the 'Variable VA' DAX is below;

Variable VA = IF(

                SELECTEDVALUE(PARTARCONE_KITS[Category])="SOC",

                    CALCULATE(SUMX(PARTARCONE_KITS,PARTARCONE_KITS[DMC 01/23])/(1-'VA SOC Parameter'[VA Parameter Value]/100)*'VA SOC Parameter'[VA Parameter Value]/100,

                        FILTER(PARTARCONE_KITS,PARTARCONE_KITS[Category]="SOC")),

                IF(

                    SELECTEDVALUE(PARTARCONE_KITS[Category])="RBOM",

                        CALCULATE(SUMX(PARTARCONE_KITS,PARTARCONE_KITS[DMC 01/23])/(1-'VA RBOM'[VA RBOM Value]/100)*'VA RBOM'[VA RBOM Value]/100,

                            FILTER(PARTARCONE_KITS,PARTARCONE_KITS[Category]="RBOM")),

                IF(SELECTEDVALUE(PARTARCONE_KITS[Category])="Memories",

                    CALCULATE(SUMX(PARTARCONE_KITS,PARTARCONE_KITS[DMC 01/23])/(1-'VA Memories'[VA Memories Value]/100)*'VA Memories'[VA Memories Value]/100,

                        FILTER(PARTARCONE_KITS,PARTARCONE_KITS[Category]="Memories")),0))

                )

The measure works fine as I get the correct results based on user input, however my problem is that the matrix visual can not summarize and show the total for the column. The last colunm of 'Total Cost' is a sum of the 'Variable VA' measure and the 'Total DMC' measure which also is not summarizing due to the 'Variable VA' column.

Appreciate any help on this,

Thank you !

Ilya

[Greg_Deckler · Accepted solution]

@ilyav First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[ilyav]

@Greg_Deckler Thanks for all the information! I followed your suggestion here Measure Totals, The Final Word - Microsoft Power BI Community and it works great now.

Have to say it's quite weird but it is what it is 🙂 good thing i'm not using measure with matrixes too often!

Thanks again,

Ilya

[Greg_Deckler]

@ilyav Doesn't have to be this way. Please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e