Legend from multiple columns clustered graph

[Leana]

Hi, could I have help replicating the below table in Power BI please? 

It's from a large public dataset with about 25,000 rows. 

One colour represents the percenage of SEN, EHCP and no SEN pupils from one particular school, the red represents the data from all primary schools across England and the blue represents all schools (primary and secondary) from across England.

I know how to do three separate graphs for each colour but not how to have them all on a clustered graph as the individual school is within the national primary which is within the national overall.

Many thanks for any help! 

[v-robertq-msft · Accepted solution]

Hi, @Leana 

According to your description, I can roughly understand your requirement, I think you can achieve this using three measures, you can try these measures:

Before creating the measure, you can create a calculated table for the school name slicer:

School name =

SUMMARIZE('SEN/EHCP/non SEN pivot',[school_name])

Then create these measures:

One particular school =

COUNTX(FILTER('SEN/EHCP/non SEN pivot',[school_name]=SELECTEDVALUE('School name'[school_name])),[school_name])

all primary schools across England =

COUNTX(FILTER('SEN/EHCP/non SEN pivot',[phase_type_grouping]="State-funded primary"),[region_code])

all schools =

COUNT('SEN/EHCP/non SEN pivot'[school_name])

Then create a clustered column chart and a slicer and place and set color like this:

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Leana]

Please see working link attached below:

External school data