Forum Discussion
Categorise average percentages
Hi all,
I did a search & couldn't find anything to help, so thought I'd ask here.
I'm trying to create a graph where it shows how many people fall into an attendance box. E.g 16 had 10% attendance or less, 11 had 20% or less & so on. Like so...
However, the data is over terms (4 per year) and if I select all 4 terms, it counts a person twice or more if they had 10% one term & 20% another term. Like so...
The total number of distinct people across the 4 terms is 2851.
What I have in my data is the pupil ID, the # of possible openings, the # of attendance openings, % of attendance openings, and in the excel data file I created a column called "Below %" using "IFS" to put the % of attendance into a box (e.g. "10", "20", etc).
The data for the above visual is "Below %" in the X axis & "Pupil ID" as a distinct count in the Y axis.
I'm trying to get the average % & put it into the individual boxes.
I do have a measure I created that I use elsewhere to get the average attendance,
I was hoping I could use this with Roundup & create the "boxes" with that & then have the count of people in each, but I'm struggling.
Can anyone help me work this out? I've been hours at it & getting nowhere.
Many thanks, G
Hi BassG
Here is the sample data:
Then add a calculated table:
Table 2 = GENERATESERIES(0,100,10)Finally, add a measure and create a Clustered Column Chart with it:
MEASURE = VAR _currentValue = MAX ( 'Table 2'[Value] ) RETURN COUNTROWS ( FILTER ( SUMMARIZE ( 'Table', 'Table'[Pupil ID], "_Avg", AVERAGE ( 'Table'[% Attendance Openings] ) ), [_Avg] <= _currentValue && [_Avg] > _currentValue - 10 ) )The result is as follow:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hello BassG
I hope I have understood your issue correctly; here is the solution I propose:
Step 1: Create the following measures:
Avg Possible Openings = AVERAGE('Data Term Year'[# Possible Openings])Avg Attendance Openings = AVERAGE('Data Term Year'[# Attendance Openings])Avg # Attendance Openings % = DIVIDE ( [Avg Attendance Openings], [Avg Possible Openings] )Step 2: Utilize the "Enter Data" feature in the "Home" tab of Power BI to create the table below. The table is named "Bucket" and essentially represents the "Below %" categories displayed in the chart.
Below % 10 20 30 40 50 60 70 80 90 100 Step 3: Create the following measure:
# Pupils below or equal to % = VAR _bucketPct = SELECTEDVALUE ( Bucket[Below %] ) VAR _filteredTbl = FILTER ( 'Data Term Year', [Avg # Attendance Openings %] <= ( _bucketPct / 100 ) ) VAR _uniquePupils = DISTINCT ( SELECTCOLUMNS ( _filteredTbl, "pupilID", 'Data Term Year'[pupilID] ) ) RETURN COUNTROWS ( _uniquePupils )Step 3: Construct your chart by placing the "Below %" column from the Bucket on the X-axis and the "# Pupils below or equal to %" measure on the Y-axis.
Additionally, the "Data Term Year" table requires no extra columns beyond "pupilID", "# Possible Openings", and "# Attendance Openings", as the percentage bucket is managed using a separate table.
I hope this resolves your issue. If you continue to encounter errors, please provide a sample anonymized dataset along with any relevant screenshots or data to better understand your query.Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTreeHi Udit,
Thank you hugely. I really appreciate the time & effort that you've put into this.
It almost does what I want, but not quite, and that's my fault for not really describing it properly. I've beentrying to work out how to adapt it to do what I'm needing, but not quite grasping what I'm doing.Taking this as an example using the solution you provided, there are 12 pupils in Term 3. I want it to show how many got between 71-80%, 81-90%, 91-100%. So if there are 12 pupils & 8 had over 90% attendance, 2 had over 80% but less than 90% & 2 had over 50% but less than 60%. It would show as 8 in the 100%, 2 in the 90% & 2 in the 60% (the boxes are for attendance of 0-10%, 11-20%, 21-30% & so on).
Then if I changed to all terms, it would see 12 pupils & put them in their appropriate % boxes.I can do this by adding a helper column to the data & use IFS(F2<=10,"10",F2<=20,"20", etc & get this...
Which works perfectly for when individual year & term is selected, but the moment I unselect terms, it doesn't Average the Pupil ID attendance %. That's the bit that I'm struggling with.It counts an ID more than once if it's in a different "box".
Does that make sense?
I'll try & get my test data up.Thanks again,
G
Hi BassG
Here is the sample data:
Then add a calculated table:
Table 2 = GENERATESERIES(0,100,10)Finally, add a measure and create a Clustered Column Chart with it:
MEASURE = VAR _currentValue = MAX ( 'Table 2'[Value] ) RETURN COUNTROWS ( FILTER ( SUMMARIZE ( 'Table', 'Table'[Pupil ID], "_Avg", AVERAGE ( 'Table'[% Attendance Openings] ) ), [_Avg] <= _currentValue && [_Avg] > _currentValue - 10 ) )The result is as follow:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Pupil ID Session Year Term # Possible Openings # Attendance Openings % Attendance Openings 12636 2023/24 Term 1 76 56 73.68 12521 2023/24 Term 1 76 68 89.47 23987 2023/24 Term 1 76 76 100 12378 2023/24 Term 1 76 54 71.05 23456 2023/24 Term 1 76 74 97.36 23876 2023/24 Term 1 76 74 97.36 23678 2023/24 Term 1 76 76 100 23732 2023/24 Term 1 76 72 94.73 12636 2023/24 Term 2 88 87 98.86 23987 2023/24 Term 2 88 85 96.59 12378 2023/24 Term 2 88 54 61.36 23456 2023/24 Term 2 88 83 94.31 23732 2023/24 Term 2 88 80 90.9 23876 2023/24 Term 2 88 85 96.59 12521 2023/24 Term 2 88 88 100 23678 2023/24 Term 2 88 88 100 12367 2023/24 Term 3 112 110 98.21 12398 2023/24 Term 3 112 110 98.21 12636 2023/24 Term 3 112 110 98.21 23987 2023/24 Term 3 112 112 100 12378 2023/24 Term 3 112 57 50.89 12389 2023/24 Term 3 72 70 97.22 23456 2023/24 Term 3 112 106 94.64 23732 2023/24 Term 3 112 98 87.49 23876 2023/24 Term 3 112 94 83.92 12521 2023/24 Term 3 112 110 98.21 23455 2023/24 Term 3 64 38 59.37 23678 2023/24 Term 3 112 104 92.85 12636 2023/24 Term 4 104 104 100 12521 2023/24 Term 4 104 104 100 23678 2023/24 Term 4 104 104 100 12367 2023/24 Term 4 104 102 98.07 12398 2023/24 Term 4 104 100 96.15 23732 2023/24 Term 4 104 100 96.15 23876 2023/24 Term 4 104 98 94.23 23987 2023/24 Term 4 104 97 93.26 12389 2023/24 Term 4 104 96 92.3 23456 2023/24 Term 4 104 96 92.3 12378 2023/24 Term 4 104 90 86.53 23455 2023/24 Term 4 104 88 84.61 Hello BassG
If the solution was helpful, please consider marking it as the solution. 🙂
