Forum Discussion
RANKX while excluding certain specific text values
I am trying to generate a ranked list within a table that excludes some things. Please see the image for reference. I'm a beginner learning DAX but I'm learning quick.
In the picture, a normal ranking is on the left. I have a master list of products detailed on the report in its own visualization. This part was easy.
The next part is what I'm stuck at. I can create the middle table using the following measure:
RANK =
RANKX(
ALLEXCEPT('PRODUCT','PRODUCT'[[All Markets]]],'PRODUCT'[[All Periods]]],'PRODUCT'[[Period Description Short]]]),[Sum $]).
This measure works correctly, but, as expected, when I filter out "Brand A" in the visualization, the rankings are preserved on Brands B and C.
I need to create a list where every brand EXCEPT ONE is included - in my data I have over 100 brands, so I need to know how they are ranked if you take Brand A out of the mix.
Thanks.
Thanks for your response. It got me halfway there, but the example I provided was flawed. There are about 25 brands total. I want to:
1.) Rank them all, taking out one brand from the ranking. So if on the current list, "C" has the ranks 1,4,5,7,9 in all the brands, then it will be removed so that the brands ranked 2,5,6,8,10 shift down to replace their rank (and the others follow).
2.) Display only the two brands I want displayed.
***
I found a fix that's less than ideal, but it works. I'd love to receive your input.
1.) Created a measure to zero out brand C's value:
NOBRANDC = IF( PRODUCT[BRAND]="C",0,PRODUCT[$])2.) Created a measure to rank based brand C = 0 (ranked very last).
BRANDED-RANK = RANKX( ALLEXCEPT('PRODUCT',PRODUCT[[All Markets]]],PRODUCT[[Period Description Short]]]),[NOBRANDC])Worked exactly as I need it to.
2 Replies
You can create a measure as below, see the attached pbix file.
RANK = RANKX ( ALLSELECTED ( 'product' ), CALCULATE ( SUM ( 'product'[$] ), ALLEXCEPT ( 'product', 'product'[brand], 'product'[item] ) ), , desc, DENSE )Thanks for your response. It got me halfway there, but the example I provided was flawed. There are about 25 brands total. I want to:
1.) Rank them all, taking out one brand from the ranking. So if on the current list, "C" has the ranks 1,4,5,7,9 in all the brands, then it will be removed so that the brands ranked 2,5,6,8,10 shift down to replace their rank (and the others follow).
2.) Display only the two brands I want displayed.
***
I found a fix that's less than ideal, but it works. I'd love to receive your input.
1.) Created a measure to zero out brand C's value:
NOBRANDC = IF( PRODUCT[BRAND]="C",0,PRODUCT[$])2.) Created a measure to rank based brand C = 0 (ranked very last).
BRANDED-RANK = RANKX( ALLEXCEPT('PRODUCT',PRODUCT[[All Markets]]],PRODUCT[[Period Description Short]]]),[NOBRANDC])Worked exactly as I need it to.
