DAX FUNCTION FOR TIERS?

[Anonymous]

 I want to create a table with three columns: Sales Rep, Dollar Amount and Tier. Right now all I have is the Sales Rep and Dollar amount columns. I want to dynamically group Sales Rep with  Dollar Amount so that each row is based on 5 tiers.  In other words the 1st 20% of Sales Reps with the most dollar amount value receive a tier 1....the next 20% a tier 2...etc...

Is there anyway to do this?

[Ashish_Mathur]

Hi,

Do you want a measure or a calculated column formula?

[Anonymous]

Hi  @Anonymous ,

Has your problem been solved? If it is solved, you can mark the correct answer. If it is not solved, you can provide more detailed information and we can help you better. 

Best Regards,

Liu Yang

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

[MFelix · Accepted solution]

Hi @Anonymous ,

Taking into account that you have only those two columns you can do it in the following way:

• Create a new column with the following syntax:

Tiers =
VAR rankingValue =
    DIVIDE ( RANKX ( 'Table', 'Table'[Sales] ), COUNTROWS ( 'Table' ) )
RETURN
    SWITCH (
        TRUE (),
        rankingValue < 0.20, 1,
        rankingValue >= 0.20
            && rankingValue < 0.4, 2,
        rankingValue >= 0.40
            && rankingValue < 0.6, 3,
        rankingValue >= 0.60
            && rankingValue < 0.8, 4,
        rankingValue >= .80
            && rankingValue <= 1, 5
    )