Forum Discussion
Filter Group While Using Rankx
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
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
3 Replies
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
