cancel
Showing results for
Did you mean: Regular Visitor

## 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,etc) based on the most Cases Needed being the highest priority, but assign no priority once the running total of PO Cases exceeds the needs. So in the tables below, Product A is the highest priority with 4200 cases needed, and POs 111 and 222 should be assigned highest priortity to fill the need, but PO 333 would no longer need priority because the need is filled. Is there any way to write this in DAX to make this happen?

 Product PO Cases A 111 3000 A 222 1500 A 333 600 B 444 2000 B 555 2000 C 666 1000 C 777 1000 C 888 1000 C 999 1000

 Product Cases Needed A 4200 B 4100 C 2000
1 ACCEPTED SOLUTION  Microsoft

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. 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

2 REPLIES 2  Microsoft

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. 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 Regular Visitor

This works! Thank you! Announcements #### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023. #### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers! #### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture. Top Solution Authors
Top Kudoed Authors
Users online (4,761)