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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nleuck_101
Resolver III
Resolver III

Sum measure where value is greater than 0

Hello All,

 

I'm having some trouble getting the correct total in my third column. The total I'm getting in the third column is 17. But I'm looking for 73. I need to sum the measure to added only values greater than zero. Also these measures are in a Measure table and not calcuated in their original datasets. I've tried multiple different ways and can't figure out how to accomplish this. Any help would be greatly appreciated!

First column measure: COUNT(Table[Column])
Second column measure: SUM(Table[Column]) + SUM(Table[Column]) + SUM(Table[Column])
Third column measure: IF(First measure - Second measure <= 0, 0, First Measure - Second Measure)

 

nleuck_101_0-1664201448041.png

 

4 REPLIES 4
nleuck_101
Resolver III
Resolver III

@Greg_Deckler 
@Jihwan_Kim 
@AilleryO 

I appreciate all your responses but nothing recommended gave me the correct total. I created a 4th measure to SUM the third measure. But instead of getting 73, I'm getting 186.
Fourth measure:
IF(Third Measure] <= 0,
   0,
   SUMX(
      FILTER(Table, [Third Measure] > 0),
      Third Measure
   )
)

nleuck_101_1-1664204938734.png

 

AilleryO
Memorable Member
Memorable Member

Hi,

 

It will not be easy to help you as we need more information, but at least I can help you clarify the problem you're facing. Measure are calculated as they're displayed with the filter context provided by the visual.

Which means that the total of your table is the calculation of your measures without the filter you have on your lines (can't tell you which one since the column is hidden on your screeshot).

And that's what explain the incorrect total.

Different solutions exists to control that :

1/ Use X functions like SUMX to keep the calculation row by row (using SUM will aggregate values).

2/ SUMX will need a table as first argument, you can use ALLSELECTED or FILTER to create that table based on the lines of your table.

3/Compute the difference.

 

Hope it helps otherwise do not hesitate to provide dummy datas.

 

Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your datamodel looks like or how your full-table-visualization looks like, but please try writing something like below and insert it into the visualization, and please check if it suits your requirement.

 

expected measure total fix: =
SUMX (
    DISTINCT ( 'TableName'[column name that is used in the table visual] ),
    [Second measure]
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Greg_Deckler
Super User
Super User

@nleuck_101 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


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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.