Forum Discussion

Marcin's avatar
Marcin
Icon for Helper V rankHelper V
8 years ago
Solved

DAX calculation max by group

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...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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
v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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