Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
diogobraga2
Helper IV
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

SchoolAcademic YearGradeTotal StudentsTargetPercent Enrolled
A2019-2020K1001001
A2019-202011001001
A2019-202021001001
A2019-202031011001.01
A2019-202041001001
A2019-202051001001
A2019-20206100951.052631579
A2019-2020799951.042105263
A2019-2020895951

 

The grade level calculation works as show above and below:

2019-11-25_1519.png

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. 

2019-11-25_1521.png

 

1 ACCEPTED 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.

 

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

View solution in original post

8 REPLIES 8
parry2k
Super User
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.

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)
 

2019-11-25_1735.png

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

 Capture.PNG

 

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

Thanks for sharing, I applied your suggestions. Measure worked, but Measure 2 is giving me the following error:

 

2019-11-26_0841.png

 

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.

Thanks! Any insights on why I am getting NaN?
2019-11-26_0913.png

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.

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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