Forum Discussion
RANKX while excluding certain specific text values
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.
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.