Filter Group While Using Rankx

[Anonymous]

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

[Pragati11]

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

[Anonymous · Accepted 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

[Pragati11]

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