Forum Discussion
Helper ITable showing Top 5
Hi,
I'm sure this is probably a lot easier than I'm thinking it is but I need to create a table showing a Top 5 items based on column 1.
However, because column 1 is likely to contain a lot of the same values I need to use a second column to determine the actual Top 5.
| ID | Department | Column 1 | Column 2 |
| 1 | a | 25 | 10000 |
| 2 | a | 25 | 40000 |
| 3 | a | 25 | 30000 |
| 4 | b | 25 | 25000 |
| 5 | b | 25 | 5000 |
| 6 | c | 25 | 0 |
| 7 | c | 25 | 100000 |
| 8 | c | 20 | 100000 |
| 9 | d | 20 | 75000 |
To add to this there is a slicer for department, so when a department is selected it shows the Top 5 for that department, and if no dpeartment is selected, it shows the Top5 for the whole business.
| ID | Department | Column 1 | Column 2 |
| 1 | c | 25 | 100000 |
| 2 | a | 25 | 40000 |
| 3 | a | 25 | 30000 |
| 4 | b | 25 | 25000 |
| 5 | a | 25 | 10000 |
This is what I would expect my Top 5 table to look like
Using TopN filter on the table filter doesn't seem to work as I can only use TopN once on the visual. So when viewing all items, I can set the ID to TopN = 5 and use the Column 1 as the "By Value" field, however as I have more than 5 items with the value 25 in Column 1 I am not seeing the true Top 5. i.e. the top 5 values in Column 1 by Column 2.
Any help is greatly appreciated.
greenawayr , You have use rank as suggested by parry2k , with tie-breaker to have only 5
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/
4 Replies
- parry2k
Super User
greenawayr you should look at Rank function and also following posts talks about how to break ties.
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finaleI would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Hi greenawayr ,
What is th logic for the Column2. Are you ranking based Column2?
Incase you want to break the ranks.
Measure =
RANKX(ALL('Table'[id]) , CALCULATE (SUM('Table'[Column1])) +RAND())
Now go to the visual filter and Select Top 5.
Regards,
Harsh Nathani
- amitchandak
greenawayr , You have use rank as suggested by parry2k , with tie-breaker to have only 5
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/
greenawayrThanks, this blog was nice and clear and concise and sorted 95% of it for me.
My issue is in creating the NoTieColumn, using Column 2. I might be missing something, but I can't just just append it as a decimal as intimated above, as 50000 would be greater than 100000 when used as a decimal. How do I create a No Tie Column with 2 sets of numbers, where the first value must have the greater weighting and the second value be used as a Tie Breaker?
Thanks again, I feel I'm on the right as Rank allows me to use Bottom 5 to return the highest ranked items, but just need to define that tie breaker column correctly.
