Grouping and summarising

Hi anton_p 

I have done the following with your sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIMDQGS4SHBQNLdBUS6RoBIXyA21TMGkUDsB8L5SrE6EE3BAUFwhbg1uSI0GaPZhKwVoskEqskbiCNTi+G6IFYh68LnvlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Pupil ID" = _t, Period = _t, Maths = _t, Reading = _t, Writing = _t, Gender = _t, Reg = _t, #"Year Group" = _t, #"SEN Status Code" = _t, PP = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pupil ID", Int64.Type}, {"Period", type text}, {"Maths", type text}, {"Reading", type text}, {"Writing", type text}, {"Gender", type text}, {"Reg", type date}, {"Year Group", Int64.Type}, {"SEN Status Code", type text}, {"PP", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "All", each "All"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Pupil ID", "Period", "Maths", "Reading", "Writing", "Gender", "Reg", "Year Group"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Attribute", "Value"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Category"),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Columns","All_All","All",Replacer.ReplaceText,{"Category"}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Replaced Value", {"Pupil ID", "Period", "Gender", "Reg", "Year Group", "Category"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns1",{{"Attribute", "Class"}, {"Value", "Grade"}})
in
    #"Renamed Columns"

(you can paste this into power query as a blank query and follow the steps)

 

1. Added a custom column "All" with value "All".
2. Selected "All", "PP", "SEN Status Code" (and any other characteristics column) and unpivoted them
3. Merged the resulting Attribute-Value columns with underscore separator and called the new column "Category" (you may call it Characteristics or something else)
4. Replaced value on this column "All_All" > "All"
5. Selected the 3 Class columns and unpivoted them.
6. Renamed the Attribute column to "Class" and the Value to "Grade"

Close and apply.

Then I made 3 measures, one for each grade %:

Grade% EXS = 

DIVIDE(CALCULATE(COUNTROWS(),Grades[Grade]="EXS"),COUNTROWS(),0)

replicate 2x and replace "EXS" with the other grades". Create as many measures as you have unique grades.
Format the measures as %.

 

Made a matrix visual like so:

Period filter works, so would year group or gender if you had unqiue values in the sample. If you want you could remove the Class from the columns and use it as a filter as well, no need to create separate visuals for them.

I hope this helps with your project. If my result is not what you expected please let me know what the expected outcome would have been and I will review!