Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Select distinct ID and averaged value from a complex table

Hi all,    I am new to PowerBI and this might be a simple question, but it would be great if someone can help me with that.    In the below data set, I am trying to select the people (and count t...
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    Anonymous  Ok, in that case you will get three rows as output (You banana average is not correct, you are missing last row). 

     

    Test246Out = 
VAR _Summary = SUMMARIZE(Test246Avg,Test246Avg[UserID],"Avg",AVERAGE(Test246Avg[Grades]))
VAR _AvgofAvg = AVERAGEX(_Summary,[Avg])
RETURN FILTER(_Summary,[Avg]>_AvgofAvg)

PattemManohar's avatar
PattemManohar
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous  Ok, in that case you will get three rows as output (You banana average is not correct, you are missing last row). 

 

Test246Out = 
VAR _Summary = SUMMARIZE(Test246Avg,Test246Avg[UserID],"Avg",AVERAGE(Test246Avg[Grades]))
VAR _AvgofAvg = AVERAGEX(_Summary,[Avg])
RETURN FILTER(_Summary,[Avg]>_AvgofAvg)

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Brilliant! Thank you so much!