DAX RANKX: Rank Column that ranks based on slicer selection?

[smore]

Spoiler

I have a slicer based on Table[Color] and a ranking measure that ranks items based on Table[Size]. When all colors are selected in the slicer it ranks every item. When a specific color is selected, it ranks only the items for that color starting from 1. This is exactly what I need. My problem using the Rank Measure is that Power BI will not let me use the Rank Measure as an axis in my visuals.

I know that I can use a calculated column as an axis for the visuals. So to work around the original problem, I tried to create a calculated column (Rank Column) that copies the Rank Measure. The calculated column works in ranking all items but when using the slicer, it does not rank items starting from 1, it just gives their overall rank based on all colors. How can I get the Calculated Column to rank starting from 1 based on the slicer selection?

Below is the DAX for the Rank Measure and Rank Column.
_____________________

Rank Measure =

RANKX(

ALLSELECTED('Table'),

CALCULATE(SUM('Table'[Size]))

,, DESC

)
______________________

Rank Column = 

RANKX(

         ALLSELECTED('Table'),

         CALCULATE(

                SUM('Table'[Size]),

                AllEXCEPT('Table', 'Table'[Size])

          )

          ,, DESC

)
______________________

[v-xulin-mstf · Accepted solution]

Hi @smore,

Do you want to show the overall ranking after applying the slicer?

Here is the output:

Try measure as:

Rank Measure = 
RANKX(
    ALL('Table'),
    CALCULATE(SUM('Table'[Size]))
    ,, DESC
)

Best Regards,

Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@smore ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[smore]

I am not sure how to upload a pbix on the forum so here is a google drive link to a sample.pbix:

https://drive.google.com/file/d/1EeEjuF9_iQCPQIEeS7bZVKmOIii8GqEE/view?usp=sharing

After preparing the sample pbix I realized that the Rank Column is not ranking properly. I am not sure what is wrong with the DAX code for the Rank Column. I just want it to work the same as the Rank Measure. Thanks for your reply.