Grouping and summarising

[anton_p]

Hi ALL

I need some help on how to achieve the following summary table using the data example shown below.

I want to summarise each grade percentage per cahacteristic groups, for eample, after filtering year group and period, i would like to see the following in one table.

Groups    /   %EXS   /    %GDS /      %WTS   ...

ALL

PP_No

PP_YES

SEN_N

SEN_E

..etc I have more characteristcs than shown here, so each characteristics need a line

Please advise whats the easiest way to achieve this.

My data tabels are:

a table with pupils data including characteristics

a table with pupil ID, period and grades for 3 some subjects (maths, reading and writing), grades can be EXS, WTS, GDS,, etc

I merged the 2 table to create the following sample data.

Pupil ID/ Period/ Maths/ Reading /Writing/ Pupils.Gender/ Pupils.Reg/ Pupils.Yeargroup/ Pupils.SEN Status Code/ Pupils.PP
1 /AUT/ WTS /GDS/ EXS/ M /5.3 /5/ N/ No
1/ SPR /EXS/ GDS/ EXS/ M /5.3 /5 /E/ No
3 /AUT/ WTS/ EXS/ GDS /M/ 5.4 /5 /K /Yes
3 /SPR/ WTS /EXS /EXS/ M/ 5.3/ 5/ N/ No

[dk_dk · Accepted solution]

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!

[anton_p]

I have managed to create the visual as per your instructions and, it worked perfectly (see below), much appreciated for your help!

[dk_dk]

Awesome! Happy to help and glad it worked!

[anton_p]

As i said earlier, i only started to use power bi a week ago so everything going slowly as it takes a long time to figure out to find where to do what but last night managed to apply everything as you suggested on the data and now understand how you manipulated the data to get what we need - truly amazing to come up with the solution so quickly.

Hopefully tonight I will be able to create the visual, will update the progress by tomorrow.

[anton_p]

Hi 

I am quite new to powerBI. Your solution is everything I am trying to achieve, many thanks for explaining so good and so promptly. I will apply this as soon as possible and come back to you if any issues. 
regards

anton

[anton_p]

I forgot to mention, I need the summary table per subject, if i know for one then I can duplicate this other subjects.