Ranking Averages by Criteria

Question

here is the deal... (actually I've solved it but I am sure there is an easier way)

my table is :

Department ID	Criteria 1	Criteria 2	Criteria 3
DEP 001	4	5	3
DEP 002	5	5	5
DEP 003	3	4	5
DEP 004	2	3	5
DEP 001	4	4	5
DEP 003	1	2	4
DEP 002	2	2	4
DEP 003	3	5	2
DEP 002	5	2	5
DEP 005	4	3	1
DEP 001	1	5	3
DEP 002	2	1	2
DEP 003	4	2	5
DEP 005	3	4	1
DEP 002	5	5	4
DEP 005	1	2	2
DEP 001	2	3	1
DEP 002	3	1	5

I am trying to find the top 1 average in each criteria. I've used unpivot, creating new table and then calculated column to find the result.

First Unpivoting

let
    Source = YourTableName,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Department ID", type text}, {"Criteria 1", Int64.Type}, {"Criteria 2", Int64.Type}, {"Criteria 3", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Department ID"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Second calculated Table :

Max Avg = 
VAR _cr1 =
    SUMMARIZE (
        'YourTableName (unpivoted)',
        'YourTableName (unpivoted)'[Department ID],
        'YourTableName (unpivoted)'[Attribute],
        "Criteria Avg",
            CALCULATE (
                AVERAGE ( 'YourTableName (unpivoted)'[Value] ),
                ALLEXCEPT (
                    'YourTableName (unpivoted)',
                    'YourTableName (unpivoted)'[Department ID],
                    'YourTableName (unpivoted)'[Attribute]
                )
            )
    )
RETURN
    _cr1

and finally the calculated rank column and filter by 1

RANK = 
RANKX (
    FILTER (
        'Max Avg',
        'Max Avg'[Attribute] = EARLIER ( 'Max Avg'[Attribute])
    ),
    'Max Avg'[Criteria Avg],
    ,
    DESC,
    DENSE
)

expected result is :

Can you suggest an easier way ?

This is PBix file where you can work on to suggest a faster way

userjpYe1 · Answer

Only by creating a couple of complicated measures. BTW, I'd always reshape data in PQ rather than DAX. Here is an alternative:

Create a criteria table.

Create a measure:

Attribute = 
VAR a = ADDCOLUMNS( 
            SUMMARIZE(YourTableName, YourTableName[Department ID]) ,    
            "@crit1", CALCULATE(AVERAGE(YourTableName[Criteria 1])),
            "@crit2", CALCULATE(AVERAGE(YourTableName[Criteria 2])),
            "@crit3", CALCULATE(AVERAGE(YourTableName[Criteria 3]))
        )
return 
SWITCH(SELECTEDVALUE(Criteria[Criteria]),
 "Criteria 1", MAXX(a, [@crit1]),
 "Criteria 2", MAXX(a, [@crit2]),
 "Criteria 3", MAXX(a, [@crit3])
 )

Add to a table visual

Result

Ranking Averages by Criteria

1 Answers