How to calculate a difference between two value sums in a pivot table

[KayB14]

Hi all,

I would really appreciate some help on this really manual piece of work and hope there's an easier way. Below is a dummy dataset from data.world that is similar to my real dataset.

I am working on a pivot table and I have two values in the 'values' field - one is the sum of the total number of students and the other is the sum of students who passed. I am looking for a way I can calculate the pass rate (sum of passes/sum of total) in the pivot table instead of doing it manually (as per columns R-T) and get it to look like that. I need to redo it because copying it it to the side I tried using the '% difference from' option in 'Show values as' but this does not give me the option to calculate a difference between value fields.

Student Data dummy.xlsx
	J	K	L	M	N	O	P	Q	R	S	T
2		Column Labels
3		2017/18		2018/19		2019/20			2017/18	2018/19	2019/20
4	Row Labels	Sum of Students Passed	Sum of Total	Sum of Students Passed	Sum of Total	Sum of Students Passed	Sum of Total		Pass rate	Pass rate	Pass rate
5	F	332	383	349	397	771	785		86.7%	91.1%	98.2%
6	M	249	266	253	270	562	565		93.6%	95.1%	99.5%
7	Grand Total	581	649	602	667	1,333	1,350		89.5%	92.8%	98.7%
Sheet4

Cell Formulas
Range	Formula
R5:R7	R5	=K5/L5
S5:S7	S5	=M5/L5
T5:T7	T5	=O5/P5

In my actual dataset I have to do this multiple times across a much larger pivot table with multiple row labels; so calculating isn't as simple as dragging a formula along. I can drag down but have to repopulate sideways.

FYI: my raw dataset is restricted in that I have sums of total categories (e.g. rather than having one line per student with information of each parameter in the columns, I have the totals of all students who have the same parameter).

Thank you!

 

[alansidman]

Since you have 365, then you can use Power Query, called Get and Transform on the Data Tab

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "2017/2018", each [Sum of Students Passed]/[Sum of Total]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "2018/2019", each [Sum of Students Passed_1]/[Sum of Total_2]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "2019/2020", each [Sum of Students Passed_3]/[Sum of Total_4]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"2017/2018", Percentage.Type}, {"2018/2019", Percentage.Type}, {"2019/2020", Percentage.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Sum of Students Passed", "Sum of Total", "Sum of Students Passed_1", "Sum of Total_2", "Sum of Students Passed_3", "Sum of Total_4"})
in
    #"Removed Columns"