rank across group and across all

[Anonymous]

DAX beginner help, please. I have the following data:

Org	Type	Score
ABC	Z	32
ABC	Y	29
ABC	X	33
ABC	W	30
DEF	Z	38
DEF	Y	39
DEF	X	31
DEF	W	36
GHI	Z	34
GHI	Y	34
GHI	X	34
GHI	W	34

I want to Rank by Type, then average all the Ranks by Org, then Give an Overall Rank. How do I do this?

TIA

[v-huizhn-msft]

Hi @Anonymous,

I am confusing about "I want to Rank by Type", you want to rank Score by Type? If it is, I try to reproduce your scenario as follows.

I create a calculated column using the formula.

Rank = RANKX(FILTER(Test,Test[Type]=EARLIER(Test[Type])),Test[Score],,ASC,Dense)

>>then average all the Ranks by Org, then Give an Overall Rank.

Could you please list an examle? How to average all the Ranks by Org, and give an overall rank for what?

average = CALCULATE(SUM(Test[Rank])/COUNTA(Test[Org]),ALLEXCEPT(Test,Test[Org]))

Please see the following screenshot.



If this doesn't resolve your issue, please share more details.

Best Regards,
Angelia

[Phil_Seamark]

Hi @Anonymous,

This code will do it the other way if @v-huizhn-msft's version is not what you need

Two new calculated columns

Rank = 
VAR OrgColumn = test[Org]
RETURN RANKX(
    FILTER(
        Test,
        test[Org]=OrgColumn
        ),Test[Score],,DESC,Dense)

and

Average = 
var TypeColumn = 'test'[Type]
RETURN 
    CALCULATE(
            AVERAGE(test[Rank]),
            FILTER(
                'test',
                test[Type]=TypeColumn
                )
               )