How to rank such that the original rank remains when individual items are selected

[andrew_tran]

Hi all,

I have been able to rank the data as follows. However, when I select an item individually, the rank changes. Is there a DAX solution such that the rank remains the same?

For example, item A4 has the rank of 4, which is as expected

However, when I select item A4 individually using the Name filter, the rank changes to 1, and I expect it to continue to be 4

Below is the formula used for the measure RANK_DESC:

RANK_DESC = RANKX(ALLSELECTED('Table'[SKU ID]),[SUM],,DESC,Dense)

Below is the formula used for the measure SUM:

SUM = SUM('Table'[Value])

This is what the underlying table looks like:

I have also tried to attach the underlying pbix file but was not able to do so, so my apologies. Because of this, please find instead below the raw data of the only table used in the pbix file.

Date	Category	Country	SKU ID	Name	Value
1-Jan-24	A	USA	A1	A1	220
2-Jan-24	A	USA	A1	A1	366
3-Jan-24	A	USA	A1	A1	169
4-Jan-24	A	USA	A1	A1	366
5-Jan-24	A	USA	A1	A1	531
6-Jan-24	A	USA	A1	A1	587
1-Jan-24	A	UK	A2	A2	893
2-Jan-24	A	UK	A2	A2	866
3-Jan-24	A	UK	A2	A2	68
4-Jan-24	A	UK	A2	A2	756
5-Jan-24	A	UK	A2	A2	587
6-Jan-24	A	UK	A2	A2	653
1-Jan-24	B	USA	A3	A3	38
2-Jan-24	B	USA	A3	A3	798
3-Jan-24	B	USA	A3	A3	919
4-Jan-24	B	USA	A3	A3	634
5-Jan-24	B	USA	A3	A3	599
6-Jan-24	B	USA	A3	A3	564
1-Jan-24	B	UK	A4	A4	521
2-Jan-24	B	UK	A4	A4	648
3-Jan-24	B	UK	A4	A4	405
4-Jan-24	B	UK	A4	A4	132
5-Jan-24	B	UK	A4	A4	751
6-Jan-24	B	UK	A4	A4	474
1-Jan-24	C	USA	A5	A5	525
2-Jan-24	C	USA	A5	A5	659
3-Jan-24	C	USA	A5	A5	986
4-Jan-24	C	USA	A5	A5	354
5-Jan-24	C	USA	A5	A5	118
6-Jan-24	C	USA	A5	A5	481
1-Jan-24	C	UK	A6	A6	252
2-Jan-24	C	UK	A6	A6	30
3-Jan-24	C	UK	A6	A6	795
4-Jan-24	C	UK	A6	A6	342
5-Jan-24	C	UK	A6	A6	221
6-Jan-24	C	UK	A6	A6	59

 Could any expert help to advise? Thank you so much!!

[johnt75]

You can use

Rank Desc = 
VAR BaseTable =
    CALCULATETABLE (
        SUMMARIZE( 'Table', 'Table'[Name], 'Table'[SKU ID] ),
        ALLEXCEPT( 'Table', 'Table'[Date], 'Table'[Country], 'Table'[Category] )
    )
VAR TableWithValues =
    ADDCOLUMNS ( BaseTable, "@value", [SUM] )
VAR Result =
    RANK ( DENSE, TableWithValues, ORDERBY ( [@value], DESC ) )
RETURN
    Result

[andrew_tran · Accepted solution]

However, I just realized that upon changing the formula from:

RANK_DESC = RANKX(ALLSELECTED('Table'[SKU ID]),[SUM],,DESC,Dense)

to:

RANK_DESC = RANKX(ALL('Table'[SKU ID],'Table'[Name]),[SUM],,DESC,Dense)

the formula then gave me the result I wanted

Thanks a lot for providing the tip!

[andrew_tran]

@divyed @bhanu_gautam Thank you so much for your quick response! I tried but it didn't work, the result was still the same unfortunately

[divyed]

Hello @andrew_tran ,

You are using ALLSELECTED which will respect external filters coming fro slicers hence the result. Try replacing ALLSELECTED with ALL and let us know.

RANK_DESC = RANKX(ALL('Table'[SKU ID]),[SUM],,DESC,Dense)

I hope this helps. 

Did I answer your query ? Mark this as solution if this helps, Kudos are appreciated.

Warm Regards,

Neeraj

[bhanu_gautam]

@andrew_tran , Use ALL function

RANK_DESC = RANKX(ALL('Table'[SKU ID]), [SUM], , DESC, DENSE)