Categorise average percentages

[BassG]

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,

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

[v-zhengdxu-msft · Accepted 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.

[BassG]

Thank you muchly.  This works a treat.

[quantumudit]

Hello @BassG 

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

[BassG]

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

[quantumudit]

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

[BassG]

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