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 Everyone,
i have a table visual which has Account Name, current period revenue and previous period revenue as values.
I need to give 2 slicers .
1) to select Top/Bottom/All
2) to select number between 2 and 30
Now based on the slicer selections, my table visual should show change.
eg: if I choose All then it should show all Account Name.
if Top and 5 is selected, then table should show top 5 accounts with highest current period revenue. And similar for Bottom as well.
My current solution involves creating the below measure and adding it as Visual level filter :
Solved! Go to Solution.
Top/Bottom Account =
SWITCH (
SELECTEDVALUE ( 'Top/Bottom - Account'[Selection] ),
"All", 1,
"Top",
IF (
MAX ( 'Sales'[Account Name] )
IN CALCULATETABLE (
VALUES ( 'Sales'[Account Name] ),
TOPN (
SELECTEDVALUE ( 'Drop Down - Account'[Values] ),
ALLSELECTED ( 'Sales'[Account Name] ),
[Current Period Revenue], DESC
)
),
1
),
IF (
MAX ( 'Sales'[Account Name] )
IN CALCULATETABLE (
VALUES ( 'Sales'[Account Name] ),
TOPN (
SELECTEDVALUE ( 'Drop Down - Account'[Values] ),
ALLSELECTED ( 'Sales'[Account Name] ),
[Current Period Revenue], ASC
)
),
1
)
)
Top/Bottom Account =
SWITCH (
SELECTEDVALUE ( 'Top/Bottom - Account'[Selection] ),
"All", 1,
"Top",
IF (
MAX ( 'Sales'[Account Name] )
IN CALCULATETABLE (
VALUES ( 'Sales'[Account Name] ),
TOPN (
SELECTEDVALUE ( 'Drop Down - Account'[Values] ),
ALLSELECTED ( 'Sales'[Account Name] ),
[Current Period Revenue], DESC
)
),
1
),
IF (
MAX ( 'Sales'[Account Name] )
IN CALCULATETABLE (
VALUES ( 'Sales'[Account Name] ),
TOPN (
SELECTEDVALUE ( 'Drop Down - Account'[Values] ),
ALLSELECTED ( 'Sales'[Account Name] ),
[Current Period Revenue], ASC
)
),
1
)
)
Hi @SW_Srinivas,
Perhaps you can take a look at the following link that also talked about dynamic top/bottom N if it helps for your requirement:
Solved: Dynamic Top/Bottom N Slicer - Almost have it! - Microsoft Power BI Community
Regards,
Xiaoxin Sheng
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |