Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello experts,
Using the below function, I am getting the table1
Rank:=if(not(isblank([bias])),rankx(ALLSELECTED(IBPData[Product Group]),[Bias],,0),blank()) |
table1
Table IBPData | |||
Location | Product Group | Vol | Rank |
A | Beer | 1000 | 1 |
A | Wine | 950 | 2 |
A | Water | 820 | 3 |
B | Wine | 980 | 1 |
B | Water | 950 | 2 |
B | Beer | 920 | 3 |
C | Beer | 1100 | 1 |
C | Water | 970 | 2 |
C | Wine | 810 | 3 |
However, I'd like to get the table2 (below) just showing the top 2 products with the highest vol. across all the locations:
table2
Table IBPData | |||
Location | Product Group | Vol | Rank |
A | Beer | 1100 | 1 |
A | Beer | 1000 | 2 |
B | Wine | 980 | 3 |
C | Water | 970 | 4 |
D | Beer | 960 | 5 |
A | Wine | 950 | 6 |
Solved! Go to Solution.
Rank := //Try this
IF (
NOT ( ISBLANK ( [bias] ) ),
RANKX (
ALLSELECTED ( IBPData[Product Group] ),
CALCULATE ( SUM ( IBPData[Vol] ) ),
,
DESC,
Dense
),
BLANK ()
)
//And then filter out this measure less than or equal to 2 in visual filter
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
Rank := // Try this one
IF (
NOT ( ISBLANK ( [bias] ) ),
VAR TopN := 2 // Specify the number of top products you want to include
RETURN
IF (
RANKX (
ALLSELECTED ( IBPData[Product Group] ),
CALCULATE ( SUM ( IBPData[Vol] ) ),
,
DESC,
Dense
) <= TopN,
RANKX (
ALLSELECTED ( IBPData[Product Group] ),
CALCULATE ( SUM ( IBPData[Vol] ) ),
,
DESC,
Dense
),
BLANK ()
)
)
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
Rank := //Try this
IF (
NOT ( ISBLANK ( [bias] ) ),
RANKX (
ALLSELECTED ( IBPData[Product Group] ),
CALCULATE ( SUM ( IBPData[Vol] ) ),
,
DESC,
Dense
),
BLANK ()
)
//And then filter out this measure less than or equal to 2 in visual filter
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |