Forum Discussion
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...
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)
Hi PattemManohar , thank you so much for the reply, this is a great solution!
However, there is one issue that remains unsolved,
VAR _TotalAverage = AVERAGE(Test246Avg[Grades])
This part does work in this data set, but it is not the accurate average value required to be. If counting the average of all the data, the value would be 71.8 rather than 70 (the average of the four people's average) as expected. Could you tell me how to get the correct value?
PattemManohar
Community Champion
Community ChampionAnonymous 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)
Brilliant! Thank you so much!