Forum Discussion
RankX in dax calculation
Hello All,
I have a table having data like below and my requirement is I need to show top 10 categories based on Count.
But its giving eveyrthing as taking same value as one rank .(value 5 as rank 3)
I used rank and filtered the data but not working.
Could any one please help
Data :
| Category | Count |
| Cat1 | 9 |
| Cat2 | 7 |
| Cat3 | 5 |
| Cat4 | 5 |
| Cat5 | 5 |
| Cat6 | 5 |
| Cat7 | 5 |
| Cat8 | 5 |
| Cat9 | 4 |
| Cat9 | 4 |
| Cat9 | 4 |
| Cat9 | 4 |
| cat10 | 3 |
Expected:
| Category | Count |
| Cat1 | 9 |
| Cat2 | 7 |
| Cat3 | 5 |
| Cat4 | 5 |
| Cat5 | 5 |
| Cat6 | 5 |
| Cat7 | 5 |
| Cat8 | 5 |
| Cat9 | 4 |
| Cat9 | 4 |
Hi , Anonymous
According to your description, you want to show the top 10 data in your table. Right?
Here are the steps you can refer to :
(1)For your data have repeat data , and when you put data in the table visual , it will be deduplicated.So we need to add an index column in Power Query Editor:
(2)For your need , you want to use a measure to filter the table . RIght?
We need to create a measure like this:
Flag = var _topn = TOPN(10 , ALLSELECTED('Table')) var _showindex = SELECTCOLUMNS(_topn,"index",[Index]) var _curindex = MAX('Table'[Index]) return IF(_curindex in _showindex ,1,0)(3)Then we need to put the field we need on the visual and we can meet your need:
(4)For this we can also use "What-if" parameter as a sliver to replace the "10" in our measure.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
- Greg_Deckler
Community Champion
Anonymous You could create a Rank Column like below and filter for <= 10
Rank Column = RANKX('Table',[Count],,DESC) - v-yueyunzh-msft
Community Support
Hi , Anonymous
According to your description, you want to show the top 10 data in your table. Right?
Here are the steps you can refer to :
(1)For your data have repeat data , and when you put data in the table visual , it will be deduplicated.So we need to add an index column in Power Query Editor:
(2)For your need , you want to use a measure to filter the table . RIght?
We need to create a measure like this:
Flag = var _topn = TOPN(10 , ALLSELECTED('Table')) var _showindex = SELECTCOLUMNS(_topn,"index",[Index]) var _curindex = MAX('Table'[Index]) return IF(_curindex in _showindex ,1,0)(3)Then we need to put the field we need on the visual and we can meet your need:
(4)For this we can also use "What-if" parameter as a sliver to replace the "10" in our measure.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
