Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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
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
This works! Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |