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.
I have a dataset, and would like to create a slicer so the enduser could select either the "Top 10" or "Bottom 10" of the products. Any ideas?
Here is a sample .csv:
PRODUCT,SALES
A,150
B,220
C,200
D,240
E,265
F,290
G,315
H,340
I,365
J,390
K,415
L,440
M,465
N,490
O,515
P,540
Q,565
R,590
S,615
T,640
U,665
V,690
W,715
X,740
Y,765
Z,790
AA,815
BB,840
CC,865
DD,890
EE,915
FF,940
GG,965
HH,990
Solved! Go to Solution.
Hi @Anonymous ,
Create a table with the following data:
ID Type
1 | Bottom 10 |
2 | Top 10 |
Then use the following measure:
TOP_N = VAR raking_asc = RANKX ( ALL ( Sales[SALES]; Sales[PRODUCT] ); CALCULATE ( SUM ( Sales[SALES] ) ) ) VAR ranking_desc = RANKX ( ALL ( Sales[SALES]; Sales[PRODUCT] ); CALCULATE ( SUM ( Sales[SALES] ) ); ; ASC ) RETURN SWITCH ( SELECTEDVALUE ( 'Bottom Top Slicer'[ID] ); 1; IF ( ranking_desc <= 10; SUM ( Sales[SALES] ); BLANK () ); 2; IF ( raking_asc <= 10; SUM ( Sales[SALES] ); BLANK () ) )
If you want to use it on a table with total value this measure needs to be changed.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
Create a table with the following data:
ID Type
1 | Bottom 10 |
2 | Top 10 |
Then use the following measure:
TOP_N = VAR raking_asc = RANKX ( ALL ( Sales[SALES]; Sales[PRODUCT] ); CALCULATE ( SUM ( Sales[SALES] ) ) ) VAR ranking_desc = RANKX ( ALL ( Sales[SALES]; Sales[PRODUCT] ); CALCULATE ( SUM ( Sales[SALES] ) ); ; ASC ) RETURN SWITCH ( SELECTEDVALUE ( 'Bottom Top Slicer'[ID] ); 1; IF ( ranking_desc <= 10; SUM ( Sales[SALES] ); BLANK () ); 2; IF ( raking_asc <= 10; SUM ( Sales[SALES] ); BLANK () ) )
If you want to use it on a table with total value this measure needs to be changed.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |