How to output the ID of the maximum average in Power BI?

Nodira Fozilova 0
Viewed 44

I have a table with data. I want to find the average by Criteria 1 for each Department ID (Department IDs are repeated) and output the value of the Department ID that has the maximum average. How can I do this? [I attach a table with data.]

Department ID Criteria 1
DEP 001 4
DEP 002 2
DEP 003 1
DEP 004 5
DEP 001 3
DEP 003 2
DEP 002 4
DEP 001 3
DEP 004 2
DEP 001 1
DEP 002 3
DEP 003 4 
Average(DEP 001) = (4+3+3+1)/4 = 11/4 = 2.75

Average(DEP 002) = (2+4+3)/3 = 9/3 = 3

Average(DEP 003) = (1+2+4)/3 = 7/3 = 2.3

Average(DEP 004) = (5+2)/2 = 7/2 = 3.5

MAX(Average(DEP 001), Average(DEP 002), Average(DEP 003), Average(DEP 004)) = 3.5

Result = DEP 004
powerbi-desktop powerbi
3 Answers

first let's create the average table

Modelling --> New Table

average table =
ADDCOLUMNS (
    SUMMARIZE ( YourTableName, YourTableName[Department ID ] ),
    "avg",
        CALCULATE (
            AVERAGE ( YourTableName[Criteria 1] ),
            ALLEXCEPT ( YourTableName, YourTableName[Department ID ] )
        )
)

then let's create the card which shows the max

Maximum Average =
VAR _max =
    MAX ( 'average table'[avg] )
RETURN
    LOOKUPVALUE ( 'average table'[Department ID ], 'average table'[avg], _max )

result

but, like your first post, if you have more than 1 criteria, you should unpivot and find a solution after the unpivoting ;)

Umut K0

Here is a measure that does this directly without adding bloat in your model:

ID of Max Avg = 
VAR _tbl =
    SUMMARIZE ( 
        'Table' , 
        'Table'[Department ID] ,
        "Avg" , AVERAGE ( 'Table'[Criteria 1] )
    )
VAR _top = 
    TOPN (
        1 ,
        _tbl , 
        [Avg] , 
        DESC
    )
RETURN
    CALCULATE ( 
        SELECTEDVALUE ( 'Table'[Department ID] ) , 
        _top 
    )

Here the measure is used in a Card visual using your sample data:

enter image description here

usergIl710 
CALCULATE(
    SELECTEDVALUE(tbl[Department ID])
    ,FILTER(
        VALUES(tbl[Department ID])
        ,CALCULATE(AVERAGE(tbl[Criteria 1]))=MAXX(
                                            VALUES(tbl[Department ID])
                                            ,CALCULATE(Average(tbl[Criteria 1]))
                                        )
    )
)
userLOTh10
Related