Rank by Region

[Anonymous]

Hello Experts

I need your help .

I am trying to Rank(on below data ) by Region but not able to get the right solution.

Here is my sample data. I need to sum emp count and Tenure by facility  and  get ave (Tenure /emp count ) and based on ave value rank  by Region . TIA

REGION	FACILITY	Emp Count	Tenure
USA	abc	8	101.06
USA	abc	8	101.73
USA	abc	8	102.37
USA	abc	8	103.06
USA	abc	8	103.7
USA	xyz	6	35.64
USA	xyz	5	35.85
USA	xyz	7	48.89
USA	xyz	8	50.92
USA	xyz	9	51.59
CANADA	Forest	5	16.27
CANADA	Forest	4	17.2
CANADA	Forest	4	17.54
CANADA	Forest	6	19.99
CANADA	Forest	6	20.51
CANADA	Leed	7	23.35
CANADA	Woods	7	23.94
CANADA	Woods	7	24.51
CANADA	Woods	8	25.17
CANADA	Woods	8	25.81

[amitchandak · Accepted solution]

@Anonymous , Create two measures like

Avg = Divide(sum(Table[Tenure]), sumx(summarize(Table, Table[FACILITY], Table[Emp Count]),[Emp Count]))

Rankx(allselected(Table[Region]), [Avg],,desc, dense)

For Rank Refer these links, if needed

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[Anonymous]

Hi @Anonymous ,

Could you tell me if your problem has been solved?

I think @smpa01 's solution is good.
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.

Best Regards,
Stephen Tao

[smpa01 · Accepted solution]

@Anonymous  you need two measures for this

_avg = 
DIVIDE (
    CALCULATE (
        SUM ( 'Table 1'[Emp Count] ),
        ALLEXCEPT ( 'Table 1', 'Table 1'[FACILITY] )
    ),
    CALCULATE (
        SUM ( 'Table 1'[Tenure] ),
        ALLEXCEPT ( 'Table 1', 'Table 1'[FACILITY] )
    )
)

_rank = 
RANKX (
    FILTER (
        ALLSELECTED ( 'Table 1' ),
        'Table 1'[REGION] = MAX ( 'Table 1'[REGION] )
    ),
    [_avg],
    ,
    ASC,
    DENSE
)

[amitchandak · Accepted solution]

@Anonymous , Create two measures like

Avg = Divide(sum(Table[Tenure]), sumx(summarize(Table, Table[FACILITY], Table[Emp Count]),[Emp Count]))

Rankx(allselected(Table[Region]), [Avg],,desc, dense)

For Rank Refer these links, if needed

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale