DAX calculation max by group

[Marcin]

Hi,

I am looking for healp with modeling in PowerBI model for case shown below. 

In model I have two dimension tables Users and Domains , one fact table Evaluation for Users by Skills. 

I am looking for DAX solution which will fill column in Users table with the highest Average of Score for SkillCategory like Analytics or Security. 

Lets assume I have measure AvaregeScore =  AVERAGEG('Evaluation'[Score]) which calculates Average for each user of its score. 

[v-yulgu-msft · Accepted solution]

Hi @Marcin,

Add below calculated columns in 'Evaluation' table.

Average =
CALCULATE (
    AVERAGE ( Evaluation[Score] ),
    ALLEXCEPT ( Evaluation, Users[UserID], Evaluation[Skill] )
)

Max average =
CALCULATE (
    SELECTEDVALUE ( Domian[SkillVCategory] ),
    FILTER (
        ALLEXCEPT ( Evaluation, Evaluation[Userid] ),
        Evaluation[Average]
            = CALCULATE (
                MAX ( Evaluation[Average] ),
                ALLEXCEPT ( Evaluation, Evaluation[Userid] )
            )
    )
)

Then, create column in 'Users' table with this DAX formula.

Hightest score skill =
LOOKUPVALUE ( Evaluation[Max average], Evaluation[Userid], Users[UserID] )

Best regards,

Yuliana Gu

[Greg_Deckler]

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

Also, if you could post your example data in a form that can be copied and pasted that would assist greatly. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Marcin]

Thank you for links, unfortunately I stiill don't know how to get that SkillCategory that I am looking for ( Category not Score Value)

I will be gratefull for help with this.