Forum Discussion

BassG's avatar
BassG
Icon for Helper I rankHelper I
2 years ago
Solved

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% attenda...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

quantumudit's avatar
quantumudit
Icon for Super User rankSuper User
2 years ago

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's avatar
BassG
Icon for Helper I rankHelper I
2 years ago

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