Forum Discussion
Average calculation
I Have below data.
I want to generate a column that will give me difference between below values for each Class-
- Average score of specific Class ( Like for CLass 1, average of Student A,B,C)
- Average score of all the Class (Average score of all the Class will be calculated based on the Average score generated for each Class & not base on avarage of all the students) (i.e. average of all class average generated as per above 1st item)
Student data table-
| Class | Student | Score |
| 1 | A | 2 |
| 1 | B | 3 |
| 1 | C | 4 |
| 2 | D | 1 |
| 2 | E | 2 |
| 3 | F | 5 |
| 3 | G | 3 |
| 3 | H | 5 |
| 3 | I | 2 |
| 4 | J | 3 |
| 4 | K | 1 |
| 4 | L | 3 |
That's really odd.
can you create a new test measure same as above but without the difference.
Test Measure VAR AverageAllClasses = CALCULATE ( [Average by Class], REMOVEFILTERS() ) RETURN AverageAllClassesDrop that into matrix as well and send results.
11 Replies
I think you just need a measure like this:
Average by Class = AVERAGEX ( VALUES ( Result[Class] ), CALCULATE ( AVERAGE ( Result[Score] ) ) )
VALUES ( Result[Class] ) creates a list of classes.
AVERAGEX then iterates over this list and calculates an AVERAGE for each.
The CALCULATE is needed as it forces a context transition so you only get an average for the current class.You can then use the measure in a matrix visual which you can see in this demo.
bcdobbs Thanks sir. The card is showing
Actually I am trying to add a column which will provide me the difference between average score by class & average of that class. I need a measure for the same.
Can you pls help me with the same which will provide me difference bewteen Average score of specific Class & verage score by Class
Avg Difference = VAR AverageAllClasses = CALCULATE ( [Average by Class], REMOVEFILTERS() ) RETURN [Average by Class] - AverageAllClasses
Try this.
Add a matrix visual with classes on the rows and drop this into the values.
