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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aartiladdha
Employee
Employee

Creating a measure on top of cumulative sum measure to filter for only the values greater than zero.

Hey All,

 

I have measures as below-

 

  • Cuml Inbounds - CALCULATE ([Total Supply],FILTER (ALLSELECTED ( 'DimDate' ),'DimDate'[IBPWeekStartDate] <= MAX ( 'DimDate'[IBPWeekStartDate])))
  • Cuml outbounds - CALCULATE ([Total Demand],FILTER (ALLSELECTED ( 'DimDate' ),'DimDate'[IBPWeekStartDate] <= MAX ( 'DimDate'[IBPWeekStartDate])))
  • Net Avail = Total Supply - Total Demand
  • Cuml Net Avail = CALCULATE ([Net Avail],FILTER (ALLSELECTED ( 'DimDate' ),'DimDate'[IBPWeekStartDate] <= MAX ( 'DimDate'[IBPWeekStartDate])))

Until here, I have been able to create all the measures. Now I want another measure which would essentially keep only positive Cuml Net Avail i.e If [Cuml Net Avail] < 0, 0, [Cuml Net Avail]. But I am unable to create one. I tried using Sumx but it isn't showing me right numbers. 

In below picture, I am looking for a way to calculate "New Measure" such that the total of that column also ties up.

 

Capture.PNG

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

1 REPLY 1
Greg_Deckler
Super User
Super User

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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