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 the numbers) whose average grade is above the average of the four.
| UserID | Year | Grades |
| Apple | 2000 | 90 |
| Apple | 2001 | 80 |
| Apple | 2002 | 70 |
| Banana | 2002 | 50 |
| Banana | 2004 | 70 |
| Banana | 2005 | 90 |
| Banana | 2007 | 70 |
| Banana | 2009 | 80 |
| Car | 2001 | 80 |
| Car | 2003 | 100 |
| Dog | 2005 | 40 |
In this example would be:
Apple's average is (90+80+70)/3=80
Banana's average is (50+70+90+70)/4=70
Car's average is (80+100)/2=90
Dog's average is 40
The avewrage of four is (80+70+90+40)/4 = 70
So the results would be apple and car and count 2.
This can be done in a complex SQL query but I am not sure if there is a clever way in PowerBI either in measure or DAX or some other genius methods.
Many thanks in advance.
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)
4 Replies
- PattemManohar
Community Champion
Anonymous Please create a new table as below
Test246Out = VAR _TotalAverage = AVERAGE(Test246Avg[Grades]) RETURN FILTER(SUMMARIZE(Test246Avg,Test246Avg[UserID],"Avg",AVERAGE(Test246Avg[Grades])),[Avg]>_TotalAverage)
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
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)