Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
BassG
Helper I
Helper I

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...
AAE G1.PNG
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...
AAE G2.PNG

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,

Average_Attendance = (SUM('Data Term Year'[# Attendance Openings]))/(SUM('Data Term Year'[# Possible Openings]))
 

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

1 ACCEPTED SOLUTION
v-zhengdxu-msft
Community Support
Community Support

Hi @BassG 

 

Here is the sample data:

vzhengdxumsft_0-1725866058401.png

Then add a calculated table:

Table 2 = GENERATESERIES(0,100,10)

vzhengdxumsft_2-1725866080703.png

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:

vzhengdxumsft_3-1725866335278.pngvzhengdxumsft_4-1725866346311.png

 

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.

View solution in original post

7 REPLIES 7
v-zhengdxu-msft
Community Support
Community Support

Hi @BassG 

 

Here is the sample data:

vzhengdxumsft_0-1725866058401.png

Then add a calculated table:

Table 2 = GENERATESERIES(0,100,10)

vzhengdxumsft_2-1725866080703.png

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:

vzhengdxumsft_3-1725866335278.pngvzhengdxumsft_4-1725866346311.png

 

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.

quantumudit
Skilled Sharer
Skilled Sharer

Hello @BassG 

If the solution was helpful, please consider marking it as the solution. 🙂

BassG
Helper I
Helper I

 

Pupil IDSession YearTerm# Possible Openings# Attendance Openings% Attendance Openings
126362023/24Term 1765673.68
125212023/24Term 1766889.47
239872023/24Term 17676100
123782023/24Term 1765471.05
234562023/24Term 1767497.36
238762023/24Term 1767497.36
236782023/24Term 17676100
237322023/24Term 1767294.73
126362023/24Term 2888798.86
239872023/24Term 2888596.59
123782023/24Term 2885461.36
234562023/24Term 2888394.31
237322023/24Term 2888090.9
238762023/24Term 2888596.59
125212023/24Term 28888100
236782023/24Term 28888100
123672023/24Term 311211098.21
123982023/24Term 311211098.21
126362023/24Term 311211098.21
239872023/24Term 3112112100
123782023/24Term 31125750.89
123892023/24Term 3727097.22
234562023/24Term 311210694.64
237322023/24Term 31129887.49
238762023/24Term 31129483.92
125212023/24Term 311211098.21
234552023/24Term 3643859.37
236782023/24Term 311210492.85
126362023/24Term 4104104100
125212023/24Term 4104104100
236782023/24Term 4104104100
123672023/24Term 410410298.07
123982023/24Term 410410096.15
237322023/24Term 410410096.15
238762023/24Term 41049894.23
239872023/24Term 41049793.26
123892023/24Term 41049692.3
234562023/24Term 41049692.3
123782023/24Term 41049086.53
234552023/24Term 41048884.61
quantumudit
Skilled Sharer
Skilled Sharer

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.

quantumudit_0-1725547167139.png

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.
AAE G3.PNG

 

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...
AAE G4.PNG
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".

AAE G5.PNG
Does that make sense?

I'll try & get my test data up.

Thanks again,
G

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors