topic Calculate total in a matrix without ignoring the filter in matrix in DAX Commands and Tips

Calculate total in a matrix without ignoring the filter in matrix

Mai_NGT — Fri, 19 May 2023 08:16:02 GMT

Hope you could help me this issue. I have checked several posts regarding the same issue but couldn't apply it to my problem

So, I have a list of data containing both negative, positive & 0 value (some even have decimal value such as -0.003) and I need to calculate the total in a matrix.

What makes it tricky is that the matrix shouldn't show 0 & positive value but only negative value <= -1, however, the total of this matrix should consider all negative value (exclude positive & 0 value, but still consider value of -0.003 in the total calculation). Which Dax formula should I use

Please help!!!!

Re: Calculate total in a matrix without ignoring the filter in matrix

Greg_Deckler — Wed, 10 May 2023 16:16:49 GMT

Mai_NGT You will need to change your ALLSELECTED to ALL most likely. ALLSELECTED respects the external filter from the Filters pane but ALL should bring everything in.

Also, 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

Re: Calculate total in a matrix without ignoring the filter in matrix

Mai_NGT — Mon, 22 May 2023 18:50:21 GMT

Thank you for your response. I took a look all the link but unfortunately, haven't found a concrete solution for my case. How can I still got the correct total value even when some of value has been hidden?

Re: Calculate total in a matrix without ignoring the filter in matrix

Greg_Deckler — Wed, 10 May 2023 23:17:48 GMT

Mai_NGT That's where you will need the ALL to bring the filtered rows back into context.