Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Mai_NGT
Regular Visitor

Calculate total in a matrix without ignoring the filter in matrix

Hello all, @PBICommunity 

 

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!!!!

1 ACCEPTED SOLUTION

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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?

 

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.