Help with DAX Measure

[chsardas1]

Hello,

I am trying to create a metric that distributes people according to 20% ranges based on the accumulated sales ordered in descending order. I mean,, a person is categorized as "Category 1" when their sales added to the sales greater than themselves are less than 20% of the total sales.

Example:

I have tried RANKX but the calculation never finishes and gives a resource error.

Thank you

[Anonymous · Accepted solution]

Hi @chsardas1 ,

Thanks for the reply from @parry2k , please allow me to provide another insight: 

If you are trying to sort by a range of 20% based on %Cumulative, then you can use the switch function. More information about the switch function can be found in the documentation:SWITCH function (DAX) - DAX | Microsoft Learn.

Measure = 
SWITCH(
    TRUE(),
    MAX([%Cumulative]) <= 0.2, 1,
    MAX([%Cumulative]) <= 0.4, 2,
    MAX([%Cumulative]) <= 0.6, 3,
    MAX([%Cumulative]) <= 0.8, 4,
    5
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@chsardas1 RANKX is the way to go. Check these blog posts How to use RANKX in DAX (Part 1 of 3 - Calculated Columns) - RADACAD