DAX for selecting team and average based on individuals

[sjade]

I have a table with a similar setup as follows: 

Name	Team	Skill Area	Rating
john	DE	SQL	3
john	DE	ETL	2
lauren	DE	ETL	3
sarah	DBA	SQL	2
sarah	DBA	SSRS	2
sarah	DBA	PowerBI	2
ben	BI	SQL	3
ben	BI	SSRS	2
george	BI	SQL	3
george	BI	SSRS	3

I am trying to write a DAX measure to calculate which team has the highest average and also how to calculate that average -- thanks in advance!

[tamerj1 · Accepted solution]

Hi @sjade 

I assume that the result will be displayed in a card visual and the skill can be selected using a slicer. Then you may try

Best Performing Teams =
CONCATENATEX (
    TOPN (
        1,
        ADDCOLUMNS (
            SUMMARIZE ( Table, Table[Team] ),
            "@Average", CALCULATE ( AVERAGE ( Table[AreaRating] ) )
        ),
        [@Average], DESC
    ),
    Table[Team],
    UNICHAR ( 10 )
)

Best Score =
MAXX (
    TOPN (
        1,
        ADDCOLUMNS (
            SUMMARIZE ( Table, Table[Team] ),
            "@Average", CALCULATE ( AVERAGE ( Table[AreaRating] ) )
        ),
        [@Average], DESC
    ),
    Table[Team]
)