Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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?
Solved! Go to Solution.
Hi @EMassicot ,
Taking into account that you have only those two columns you can do it in the following way:
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
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
Do you want a measure or a calculated column formula?
Hi @EMassicot ,
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.
Hi @EMassicot ,
Taking into account that you have only those two columns you can do it in the following way:
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
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
120 | |
69 | |
68 | |
57 | |
50 |
User | Count |
---|---|
166 | |
82 | |
68 | |
65 | |
54 |