Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
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.
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.
Back again 🙂
Do you know how to sum a selection from this? E.g. If I wanted to count the number of pupils in Term 3 that didn't achieve 100, how do I get it to sum the selected values (using a slicer made with the bucket).
Thanks,
G
Thank you muchly. This works a treat.
Hello @BassG
If the solution was helpful, please consider marking it as the solution. 🙂
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
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: LinkTree
Hi 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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
14 | |
13 | |
12 | |
9 |