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 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. 

 

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

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
      Marcin
      Icon for Helper V rankHelper V
      8 years ago

      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. 

  • 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