Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to Solution.
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.
@diogobraga2 can you share what measure you currently used to calculate average
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I tried avereging the calculation listed on the post: Percent Enrolled = Total Students / Target
Hi @diogobraga2 ,
We can make it divide by the distinctcount of [Grade]. Here I have created a sample for your reference.
Measure =
SUM ( 'Table'[Target] ) / SUM ( 'Table'[Total Students] )
Measure 2 =
SUMX ( 'Table', [Measure] ) / DISTINCTCOUNT ( 'Table'[Grade] )
Thanks for sharing, I applied your suggestions. Measure worked, but Measure 2 is giving me the following error:
PS: The daset has another dimension called Academic Year, besides School and Grade.
@diogobraga2 not sure why you you have comma before grade. it should be just a column from the table.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.