RankX excluding some values

[Anonymous]

Hi,

I want to exclude some value or want filter some values while calculating the Rank. I am calculating the Ranks on the basis of average ranking given on some parameters.

I am using below DAX but not getting the right answer

Divison Ranking check =

RANKX(ALL(Table),CALCULATE(AVERAGE(Score[Star Rating]),FILTER(Table,Table[Unit]<>"Group1")),,DESC,Dense)

I want to exlude Group1 while calculating the Rankx.. Need urgent help.

See: I am getting 1 in all ranking also Group1 is there in the list.

Raw table is given below

Unit	Rank Check	Average of Star Rating
Group12	1	3
Group11	1	2.76
Group10	1	2.62
Group9	1	2.57
Group8	1	2.36
Group7	1	2.33
	1	2.29
Group5	1	2.22
Group4	1	2.11
	1	2.06
Group1	1	2
Group2	1	1.95
Group3	1	1.8

@amitchandak Could you please help

 Regards

Uphar

[harshnathani]

HI @Anonymous ,

Use this measure

Rank without Grp 1 = RANKX (FILTER(ALL('Table'),'Table'[Unit] <> "Group1"), CALCULATE(AVERAGE('Table'[Average of Star Rating])))

Post this apply a visual filter

 

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi Harsh, Thanks for your  quick response. 

But I dont want to filter the Unit using visual filter I am looking for no rank if I am filtering out any group.

I have applied the same DAX and got below results

Unit	Divison Ranking check	Average of Star Rating
Group12	1	3
Group11	2	2.76
Group10	3	2.62
Group9	4	2.57
Group8	5	2.36
Group7	6	2.33
	7	2.29
Group5	8	2.22
Group4	9	2.11
	10	2.06
Group1	11	2
Group2	11	1.95
Group3	12	1.8
TBA	13

Here, I am filtering out the group2 but see group2 have 11th Rank.. Instead of this Is there any way we can make it blank or remove that group?

I wan to rank them indivually like : Group1 having 11 rating out of 13 group or 11/13(its a total group count)

Regards

Uphar

[harshnathani · Accepted solution]

Hi @Anonymous ,

Then try this measure.

Rank Without GRp 1 = 

IF(
    SELECTEDVALUE('Table'[Unit]) = "Group1", BLANK(),
RANKX (FILTER(ALL('Table'),'Table'[Unit] <> "Group1"), CALCULATE(AVERAGE('Table'[Average of Star Rating]))))

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Hi Harsh. 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

[harshnathani]

Hi @Anonymous ,

Can you create a separate post for this as too many questions in one post will confuse future users of the community.

In the other post, pls share proper data in text format, your calendar table structure and your data models to help you on this.

Appreciate your support on this.

Thanks

HN