Rank Relative to a Chosen Value's Rank

[Anonymous]

Data: I have 1 table with a column, and sales for that column. I have ranked them based on sales. 

This table has a slicer on ItemSalesRegion (i.e. A,B,C,D)

ItemSalesRank

A(Region)	10(Sales)	3(Rank)
B	20	2
C	58	1
D	5	4

Requirement: Based on the value chosen in slicer I want to find relative rank of every item in Column 1. For example if I chose B, I want the following output. I also want the whole data set to show and the slicer not filter to B(chosen Filter). 

ItemSalesRankRelative To Chosen B

A	10	3	-1
B	20	2	0
C	58	1	1
D	5	4	-2

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

I believe you will need a supporting Table to achieve this

If RANK is a calculated column, then you can create a calculated table from the modelling tab

Items =
ALL ( Table1[Item], Table1[RANK] )

And use it as a slicer.

Then use following MEASURE

Relative RANK =
VAR myrank =
    SELECTEDVALUE ( Items[RANK] )
RETURN
    myrank - SELECTEDVALUE ( Table1[RANK] )

[v-piga-msft]

Hi @Anonymous,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

I believe you will need a supporting Table to achieve this

If RANK is a calculated column, then you can create a calculated table from the modelling tab

Items =
ALL ( Table1[Item], Table1[RANK] )

And use it as a slicer.

Then use following MEASURE

Relative RANK =
VAR myrank =
    SELECTEDVALUE ( Items[RANK] )
RETURN
    myrank - SELECTEDVALUE ( Table1[RANK] )

[Zubair_Muhammad · Accepted solution]

@Anonymous

See attached file as well