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.
Dear PBI enthusiasts!
I have a requirement where I need to show the Top N of a column '%Profit' and if there is a tie then I need to use the column 'Total Sales'. I am using RankX for my first column but not getting the second column to be used when there is a tie.
Any help would be greatly appreciated and thank you in advance!
Solved! Go to Solution.
Hi @DivyaReddy ,
try this measure
Rank =
VAR Rank1 =
RANKX(ALL(Facts),
[% Profit]
)
VAR Rank2 =
VAR CurrentProfit = [% Profit]
RETURN
RANKX(
CALCULATETABLE(
FILTER(
ADDCOLUMNS(
Facts,
"@Total Sales",[Total Sales],
"@%Profit",[% Profit]
),
[% Profit] = CurrentProfit
),
ALL('Facts')),
[Total Sales]
)
RETURN
Rank1*Rank2
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @DivyaReddy ,
try this calculated column:
This seems to have worked in theory but not sure why I get "Parameter not correct type" for "EARLIER('Facts14'[%Profit])" and EARLIER('Facts14'[Total Sales]) when I substituted my actual column and table names They They are measures (not columns) in my actual data not sure if that matters?
Hi @DivyaReddy ,
try this measure
Rank =
VAR Rank1 =
RANKX(ALL(Facts),
[% Profit]
)
VAR Rank2 =
VAR CurrentProfit = [% Profit]
RETURN
RANKX(
CALCULATETABLE(
FILTER(
ADDCOLUMNS(
Facts,
"@Total Sales",[Total Sales],
"@%Profit",[% Profit]
),
[% Profit] = CurrentProfit
),
ALL('Facts')),
[Total Sales]
)
RETURN
Rank1*Rank2
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Looking into it - thank u so much!
HI @DivyaReddy ,
Did I answer your question? Mark this post as a solution if I did!
not very clear for me
Sorry abt that - updated the description and hopefully it is better now.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |