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.
Hello,
Your help on this will be appreciated.
There are 2 tables and I wanna creat a ranking measure that rank by "Net Sales" measure for "Group".
Net Sales = SUM(Sales) - SUM(Returns)
I want to see the Group ranking by net sales like below...
and no matter what amount of net sales does Group "Others" get, always put "Others" at the last one of the ranking.
How could I write the RANKX DAX ?
Thanks in advance.
Solved! Go to Solution.
Hi @Chelsea_Yu
Please use the following measure
Rank =
VAR ThisGroup = SELECTEDVALUE ( 'Channel table'[Group] )
VAR Ranking =
RANKX (
FILTER ( ALLSELECTED ( 'Channel table'[Group] ), 'Channel table'[Group] <> "Others" ),
[Net Sales]
)
RETURN
IF ( ThisGroup = "Others", Ranking + 1, Ranking )
Another method:
Rank =
VAR ThisGroup = SELECTEDVALUE ( 'Channel table'[Group] )
VAR SelectedGroups = ALLSELECTED ( 'Channel table'[Group] )
VAR T1 =
ADDCOLUMNS (
SelectedGroups,
"@Rank",
RANKX (
FILTER ( ALLSELECTED ( 'Channel table'[Group] ), 'Channel table'[Group] <> "Others" ),
[Net Sales]
)
)
VAR CurrentRank =
RANKX (
FILTER ( ALLSELECTED ( 'Channel table'[Group] ), 'Channel table'[Group] <> "Others" ),
[Net Sales]
)
VAR MaxRank = MAXX ( T1, [@Rank] )
RETURN
IF ( ThisGroup = "Others", MaxRank + 1, CurrentRank )
Hi @Chelsea_Yu
Please use the following measure
Rank =
VAR ThisGroup = SELECTEDVALUE ( 'Channel table'[Group] )
VAR Ranking =
RANKX (
FILTER ( ALLSELECTED ( 'Channel table'[Group] ), 'Channel table'[Group] <> "Others" ),
[Net Sales]
)
RETURN
IF ( ThisGroup = "Others", Ranking + 1, Ranking )
Another method:
Rank =
VAR ThisGroup = SELECTEDVALUE ( 'Channel table'[Group] )
VAR SelectedGroups = ALLSELECTED ( 'Channel table'[Group] )
VAR T1 =
ADDCOLUMNS (
SelectedGroups,
"@Rank",
RANKX (
FILTER ( ALLSELECTED ( 'Channel table'[Group] ), 'Channel table'[Group] <> "Others" ),
[Net Sales]
)
)
VAR CurrentRank =
RANKX (
FILTER ( ALLSELECTED ( 'Channel table'[Group] ), 'Channel table'[Group] <> "Others" ),
[Net Sales]
)
VAR MaxRank = MAXX ( T1, [@Rank] )
RETURN
IF ( ThisGroup = "Others", MaxRank + 1, CurrentRank )
Hi @tamerj1 ,
Yes this is what I need!!!
Thank you tamerj1, and sorry for the late reply. 🙂
Best Regards,
Chelsea
Hi @Chelsea_Yu,
You may try this Calculated column.
Ranking =
VAR rankExceptOthers =
RANKX (
FILTER ( 'Channel table', 'Channel table'[Group] <> "Others" ),
CALCULATE('Channel table'[Net Sales],ALLEXCEPT('Channel table','Channel table'[Group])),
,
DESC,
DENSE
)
VAR countGroup =
CALCULATE ( DISTINCTCOUNT ( 'Channel table'[Group] ), ALL ( 'Channel table' ) )
RETURN
IF ( 'Channel table'[Group] = "Others", countGroup, rankExceptOthers )
The result looks like this.
Also, attached the pbix file as reference.
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
Hi Caiyun, @v-cazheng-msft
Thanks for your reply.
But I've found that it dosen't work when I want to rank the group within a certain period of time.
Please refer to the table below, the ranking is not accroding to the net sales during 5/13 - 5/31.
Your reply seems pretty close to the solution, could the way may be creating a ranking measure by temp table in dax instead of a column ?
Best Regards,
Chelsea
Hi @Chelsea_Yu
you my try
RANKX (
VALUES ( Channel[Groups] ),
IF (
Channel[Groups] = "Others",
0,
[Net Sales]
)
)
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |