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

[aartiladdha]

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.

[Greg_Deckler · Accepted solution]

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.