Forum Discussion
Calculate Straight or Weighted Average for the Total
The dataset has grade level data for schools (subset shown below).
The goal is to calculate percentage of total for each grade AND for the whole school using this dataset.
Percent Enrolled = Total Students / Target
| School | Academic Year | Grade | Total Students | Target | Percent Enrolled |
| A | 2019-2020 | K | 100 | 100 | 1 |
| A | 2019-2020 | 1 | 100 | 100 | 1 |
| A | 2019-2020 | 2 | 100 | 100 | 1 |
| A | 2019-2020 | 3 | 101 | 100 | 1.01 |
| A | 2019-2020 | 4 | 100 | 100 | 1 |
| A | 2019-2020 | 5 | 100 | 100 | 1 |
| A | 2019-2020 | 6 | 100 | 95 | 1.052631579 |
| A | 2019-2020 | 7 | 99 | 95 | 1.042105263 |
| A | 2019-2020 | 8 | 95 | 95 | 1 |
The grade level calculation works as show above and below:
The issue is when I remove grades to calculate the percent of total for the school, when I try AVG it does not work properly. What am I doing wrong and what is the recommended way of doing this? What is the best practice to calculate straight and weighted average for the total when there is a dimension like 'grades' in this example.
Hi diogobraga2 ,
As the error message, In your formual, DISTINCTCOUNT should not be used like thay, please refer to my formula again.
Measure 2 = SUMX ( 'Table', [Measure] ) / DISTINCTCOUNT ( 'Table'[Grade] )Or you can share your pbix to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
8 Replies
diogobraga2 can you share what measure you currently used to calculate average
I tried avereging the calculation listed on the post: Percent Enrolled = Total Students / Target
- This is the actual calcution:% Total Enrolled M = if(student_status_count[Total Students M]/student_status_count[Target M]>0,student_status_count[Total Students M]/student_status_count[Target M],0)
