Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Hi, Thank you that puts me on the track to get it done
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |