Forum Discussion
DAX FUNCTION FOR TIERS?
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?
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 )
3 Replies
- MFelix
Super User
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 ) 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.
- Ashish_Mathur
Hi,
Do you want a measure or a calculated column formula?
