Ranking - masking non selected Items

[DerekRadue]

Hi

I have data similar to the table below

	KPI 1	KPI 2	KPI 3
Store A	300	30	522
Store B	100	75	174
Store C	200	15	87
Store D	400	45	348
Store E	500	90	435
Store F	600	60	261

And would like to show it as per the example below

After selecting a slicer for Store A - the visualization/s should show like below ; ie the selected Store + the KPI value, but shown in the ranked position relative to all other stores - while masking all other store detail 

KPI 1	Kpi 2	KPI 3
-	-	-
-	Store A (30)	-
Store A (300)	-	-
-	-	-
-	-	-
-	-	Store A (522)

Any help would be greatly appreciated (even if we can do it for 1 KPI)

Thank you

[v-yiruan-msft · Accepted solution]

Hi @DerekRadue ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. Please note that this method is only suitable for selecting one store. If you select multiple stores, the result will be incorrect...

1. Create a table with the below formula

Rank = GENERATESERIES(1,DISTINCTCOUNT('Table'[Store]),1)

2. Create 3 measures to get KPI 1, KPI 2 and KPI 3 to display in different positions base on the ranking

NKPI1 = 
VAR _rankKPI1 =
    RANKX (
        ALL ( 'Table'[Store] ),
        CALCULATE ( SUM ( 'Table'[KPI 1] ) ),
        ,
        ASC,
        DENSE
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Rank'[Value] ) = _rankKPI1,
        CALCULATE (
            SUM ( 'Table'[KPI 1] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Store] IN ALLSELECTED ( 'Table'[Store] )
            )
        ),
        BLANK ()
    )

Best Regards

[DerekRadue]

Hi, Thank you that puts me on the track to get it done