Forum Discussion
Ranking For few columns
Hi
I have a sample below table, which i hav the data this way.
| Productname | Subcategory | revenue |
| Coolers | homecooler | 345 |
| Refregirator | Mini refregiration | 543 |
| Fulltotal | Total | 456 |
| Bikes | Bicycles | 887 |
| Cars | Battery vehicles | 667 |
Now i want to create two rank
1.) based on Revenue for Product category
2.) based on Revenue for subcategory
When i create a RANK for Product category, my condition is "Fulltotal" should not be included in the Rank which always need to come last
When i create a RANK for Subcategory, my condition is "Total" should not be included in the Rank which always need to come last
SO my out put should be like this , Even though Total is high it is coming as Last, How to write the DAX for the below
| Productname | Subcategory | revenue | RankProd | RankSub |
| Bikes | Bicycles | 887 | 1 | 1 |
| Cars | Battery vehicles | 667 | 2 | 2 |
| Refregirator | Mini refregiration | 543 | 3 | 3 |
| Coolers | homecooler | 345 | 4 | 4 |
| Total | Total | 456 | 5 | 5 |
Thanks,
2 Replies
Anonymous Try:
Column = VAR __Count = COUNTROWS('Table5') RETURN IF([Productname]="Fulltotal",__Count,RANKX(FILTER(ALL('Table5'),[Productname]<>"Fulltotal"),[revenue],,DESC)) Column = VAR __Count = COUNTROWS('Table5') RETURN IF([Subcategory]="Total",__Count,RANKX(FILTER(ALL('Table5'),[Subcategory]<>"Total"),[revenue],,DESC))Anonymous , Try measures like, assume revenue is a measure
Product Rank =
rankx(allselected(Table[product name]), [revenue])
Sub category Rank=
rankx(allselected(Table[subCategory name]), [revenue])
For Rank Refer these links
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-finale
