Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mossmic1
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?

 

ProductPOCases
A1113000
A2221500
A333600
B4442000
B5552000
C6661000
C7771000
C8881000
C9991000

 

 

 

ProductCases Needed
A4200
B4100
C2000
1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

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

 

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] )

c1.PNG

 

Regards

View solution in original post

2 REPLIES 2
v-ljerr-msft
Employee
Employee

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

 

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] )

c1.PNG

 

Regards

This works! Thank you!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.