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,
Its a very simple query but I am not able to get the results or not able to find the logic. I want filter some values from group to give the ranking.
Raw Data
Group | Item | Value | Rank |
One | item1 | 778 | 1 |
One | item2 | 617 | 2 |
One | item3 | 540 | 3 |
Three | Item4 | 847 | 2 |
Three | Item6 | 873 | 1 |
Three | Item9 | 781 | 3 |
Two | item8 | 512 | 3 |
Two | Item10 | 785 | 1 |
Two | item5 | 623 | 2 |
Further I used Rankx to calcualte the group wise rank
RankXDescMeasure2 = RANKX( ALL('Table'[Group]), CALCULATE( SUM('Table'[Value]) ), , DESC )
Group | Value | Rank1 |
One | 1935 | 2 |
Three | 2501 | 1 |
Two | 1920 | 3 |
How would I filter one or two or three and give a new rank like below:
Group | Value | Rank1 |
Three | 2501 | 1 |
Two | 1920 | 2 |
Please help !
Regards
Uphar
Solved! Go to Solution.
Hi, Thanks it works..
If you could also help me with Month on Month Rank change DAx.. My rank is changing month on month basis for same group.
I want to check the Rank difference of this month to last month (Trend)
Last Month Ranking
Month | Unit | Divison Ranking check | Average of Star Rating |
Feb | Group12 | 1 | 3 |
Feb | Group11 | 2 | 2.76 |
Feb | Group10 | 3 | 2.62 |
Feb | Group9 | 4 | 2.57 |
Feb | Group8 | 5 | 2.36 |
Feb | Group7 | 6 | 2.33 |
Feb | 7 | 2.29 | |
Feb | Group5 | 8 | 2.22 |
Feb | Group4 | 9 | 2.11 |
Feb | 10 | 2.06 | |
Feb | Group1 | 11 | 2 |
Feb | Group2 | 11 | 1.95 |
Feb | Group3 | 12 | 1.8 |
Feb | TBA | 13 |
This month ranking
Month | Unit | Divison Ranking check | Trend |
Mar | Group12 | 13 | 12 |
Mar | Group11 | 7 | 5 |
Mar | Group10 | 4 | 1 |
Mar | Group9 | 3 | -1 |
Mar | Group8 | 3 | -2 |
Mar | Group7 | 3 | -3 |
Mar | 4 | -3 | |
Mar | Group5 | 10 | 2 |
Mar | Group4 | 2 | -7 |
Mar | 9 | -1 | |
Mar | Group1 | 9 | -2 |
Mar | Group2 | 8 | -3 |
Mar | Group3 | 9 | -3 |
Mar | TBA | 4 | -9 |
Tend is representing this month to last month rank difference.. I am new in powerbi so not able to apply the Time intellegence function.. I believe you can answer this question too..
Regards
Uphar
HI @Anonymous ,
You will have to modify your dax something as below:
RankXDescMeasure2 = RANKX( FILTER(ALL('Table'[Group]), 'Table'[Group] <> "Your Value1" && 'Table'[Group] <> "Your Value2"), CALCULATE( SUM('Table'[Value]) ), , DESC )
Also there is a similar thread for this. Check following:
https://community.powerbi.com/t5/Desktop/Rankx-with-filter/td-p/63457
Thanks,
Pragati
Hi, Thanks it works..
If you could also help me with Month on Month Rank change DAx.. My rank is changing month on month basis for same group.
I want to check the Rank difference of this month to last month (Trend)
Last Month Ranking
Month | Unit | Divison Ranking check | Average of Star Rating |
Feb | Group12 | 1 | 3 |
Feb | Group11 | 2 | 2.76 |
Feb | Group10 | 3 | 2.62 |
Feb | Group9 | 4 | 2.57 |
Feb | Group8 | 5 | 2.36 |
Feb | Group7 | 6 | 2.33 |
Feb | 7 | 2.29 | |
Feb | Group5 | 8 | 2.22 |
Feb | Group4 | 9 | 2.11 |
Feb | 10 | 2.06 | |
Feb | Group1 | 11 | 2 |
Feb | Group2 | 11 | 1.95 |
Feb | Group3 | 12 | 1.8 |
Feb | TBA | 13 |
This month ranking
Month | Unit | Divison Ranking check | Trend |
Mar | Group12 | 13 | 12 |
Mar | Group11 | 7 | 5 |
Mar | Group10 | 4 | 1 |
Mar | Group9 | 3 | -1 |
Mar | Group8 | 3 | -2 |
Mar | Group7 | 3 | -3 |
Mar | 4 | -3 | |
Mar | Group5 | 10 | 2 |
Mar | Group4 | 2 | -7 |
Mar | 9 | -1 | |
Mar | Group1 | 9 | -2 |
Mar | Group2 | 8 | -3 |
Mar | Group3 | 9 | -3 |
Mar | TBA | 4 | -9 |
Tend is representing this month to last month rank difference.. I am new in powerbi so not able to apply the Time intellegence function.. I believe you can answer this question too..
Regards
Uphar
Hi @Anonymous ,
It will be nice to have a new thread for this as this will require a new DAX. It just helps in keeping the issues differently for everyone.
The query raised in this thread looks like is resolved.
Thanks,
Pragati
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 |