Forum Discussion
Solved
Prioritize Based on Need with Running Totals
I am trying to create a priority ranking for POs based on the number of cases needed by Produce. The scenario can have the Product across multiple POs. I want to assign priority (in numbers: 1,2,3,et...
Hi mossmic1,
If I understand you correctly, you should be able to use the formulas below to create three calculate columns get your expected result in your scenario. :smileyhappy:
1. Rank PO in Product level.
Column 1 = VAR c = Table1[Cases] RETURN CALCULATE ( RANK.EQ ( c, Table1[Cases], DESC ), ALLEXCEPT ( Table1, Table1[Product] ) )2. Running total of Previous Rank PO cases.
Column 2 = CALCULATE ( SUM ( Table1[Cases] ), FILTER ( ALL ( Table1 ), Table1[Product] = EARLIER ( Table1[Product] ) && Table1[Column 1] < EARLIER ( Table1[Column 1] ) ) )3. Final Rank,
Column 3 = IF ( Table1[Column 2] <= RELATED ( Table2[Cases Needed] ), Table1[Column 1] )
Regards
v-ljerr-msft
Microsoft Employee
Microsoft EmployeeHi mossmic1,
If I understand you correctly, you should be able to use the formulas below to create three calculate columns get your expected result in your scenario. :smileyhappy:
1. Rank PO in Product level.
Column 1 =
VAR c = Table1[Cases]
RETURN
CALCULATE (
RANK.EQ ( c, Table1[Cases], DESC ),
ALLEXCEPT ( Table1, Table1[Product] )
)
2. Running total of Previous Rank PO cases.
Column 2 =
CALCULATE (
SUM ( Table1[Cases] ),
FILTER (
ALL ( Table1 ),
Table1[Product] = EARLIER ( Table1[Product] )
&& Table1[Column 1] < EARLIER ( Table1[Column 1] )
)
)
3. Final Rank,
Column 3 = IF ( Table1[Column 2] <= RELATED ( Table2[Cases Needed] ), Table1[Column 1] )
Regards
This works! Thank you!