cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Running total of minimum of two expressions

Hello Power BI community,

 

I'm working on a maintenance dashboard and have an issue with a running total.  I have calculated measures for tool supply and tool demand.  The minimum of these 2 measures is the number of tools we will build in the given month.  The tools are prioritized, and I want to calculate a running total of the minimum column to give us an idea of when we will run out of materials.

 

PriorityToolSupplyDemandMinimum
1Tool A9711297
2Tool B5688
3Tool C4400
4Tool D141313
5Tool E201111
6Tool F1622

 

My equation looks like this:

 

Running Total = CALCULATE( MIN( [Supply] , [Demand] ) , FILTER( ALL( Asset_Mapping ),  Asset_Mapping[ID] <= MAX( Asset_Mapping[ID] ) ) )

 

 

   Measure Measure ResultExpected Result
PriorityToolSupplyDemandMinimumRunning TotalRunning Total
1Tool A97112979797
2Tool B5688120105
3Tool C4400120105
4Tool D141313133118
5Tool E201111144129
6Tool F1622146131

 

You can see that I don't get the result I expected.  It seems to calculate the minimum of the running total and I would like to calculate the running total of the minimum.  Any help is greatly appreciated. Thanks in advance.

 

Cheers,

Linds

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @LindsW8

I don’t have dataset with column Asset_Mapping[ID] as you used, so I use column [Priority] in your provided table instead. Anyway it works out your expected result.

Try formula below to calculate running total of minimum of two expressions

Measure 3 =
CALCULATE (
    SUMX ( Sheet2, MIN ( [Supply], [Demand] ) ),
    FILTER ( ALL ( Sheet2 ), Sheet2[Priority] <= MAX ( Sheet2[Priority] ) )
)

21.png

Best Regards

Maggie

View solution in original post

2 REPLIES 2
Community Support
Community Support

Hi @LindsW8

I don’t have dataset with column Asset_Mapping[ID] as you used, so I use column [Priority] in your provided table instead. Anyway it works out your expected result.

Try formula below to calculate running total of minimum of two expressions

Measure 3 =
CALCULATE (
    SUMX ( Sheet2, MIN ( [Supply], [Demand] ) ),
    FILTER ( ALL ( Sheet2 ), Sheet2[Priority] <= MAX ( Sheet2[Priority] ) )
)

21.png

Best Regards

Maggie

View solution in original post

Thank you @v-juanli-msft, that works perfectly!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors