cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper IV

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.

1 ACCEPTED SOLUTION
Community Support

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] )``````

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
8 REPLIES 8
Super User

@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.

Helper IV

I tried avereging the calculation listed on the post: Percent Enrolled = Total Students / Target

Helper IV
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)

Community Support

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] )
``````

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Helper IV

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.

Super User

@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.

Helper IV
Thanks! Any insights on why I am getting NaN?
Community Support

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] )``````

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors