Answered by:
How to add new column in PivotTable.
Question

Hi all,
I am having following pivot table.
Unit Grp 1 Grp 2 Perm Parttime Cons Total gr1 Perm Parttime Cons Total gr2 Row 1 1 2 5 8 21 88 5 114 Row 2 34 5 6 45 232 55 4 291 Row 3 76 76 6 158 21 5 1 27 Row 4 56 8 8 72 32 23 55 110 Row 5 9 5 2 16 4 6 5 15 In my data model I have one Column which have values for Gr1 and Gr2. And another column for Level (Perm, Part, Cons).
This values may variy. new rows can be added or if Some column might not have value it will not display.
I want to add some calculation in from of my pivot table.
Following is the table of my requirement where Columns from Grp2 will be subtracted from columns from Grp1.
Unit Grp 1 Grp 2 Net Perm Parttime Cons Total gr1 Perm Parttime Cons Total gr2 Perm Parttime Cons Total Row 1 1 2 5 8 21 88 5 114 20 86 0 106 Row 2 34 5 6 45 232 55 4 291 198 50 2 246 Row 3 76 76 6 158 21 5 1 27 55 71 5 (131) Row 4 56 8 8 72 32 23 55 110 24 15 47 38 Row 5 9 5 2 16 4 6 5 15 5 1 3 (1) In Net section it will calculate difference. For eg. Gr2 Col Perm  Gp1 Col Perm and so on.
How can i do this in single pivot table.
Please suggest.
Thanks.
Wednesday, October 15, 2014 1:52 PM
Answers

HI
You can add measure:
Net Perm:=CALCULATE(SUM([Perm]);[Grp]="Grp 2")  CALCULATE(SUM([Perm]);[Grp]="Grp 1")
note that ";" in some excel version can be replace by ","
[Grp] column can have different name in your data model, which was not presented
Gordonik
 Proposed as answer by Ed Price  MSFTMicrosoft employee Saturday, October 18, 2014 7:50 AM
 Marked as answer by Michael Amadi Thursday, October 30, 2014 8:58 AM
Thursday, October 16, 2014 8:37 PM 
I don't know what your model structure looks like, so I've made some general assumptions in the table and column names below, but this is the general format to follow to create this measure:
Group 1 Perm:= CALCULATE( [Perm] , 'DimGroup'[Group] = "Grp 1" ) Group 2 Perm:= CALCULATE( [Perm] , 'DimGroup'[Group] = "Grp 2" ) Net Perm:= [Group 1 Perm]  [Group 2 Perm]
 Proposed as answer by Ed Price  MSFTMicrosoft employee Saturday, October 18, 2014 7:49 AM
 Marked as answer by Michael Amadi Thursday, October 30, 2014 8:58 AM
Wednesday, October 15, 2014 5:22 PM
All replies

I don't know what your model structure looks like, so I've made some general assumptions in the table and column names below, but this is the general format to follow to create this measure:
Group 1 Perm:= CALCULATE( [Perm] , 'DimGroup'[Group] = "Grp 1" ) Group 2 Perm:= CALCULATE( [Perm] , 'DimGroup'[Group] = "Grp 2" ) Net Perm:= [Group 1 Perm]  [Group 2 Perm]
 Proposed as answer by Ed Price  MSFTMicrosoft employee Saturday, October 18, 2014 7:49 AM
 Marked as answer by Michael Amadi Thursday, October 30, 2014 8:58 AM
Wednesday, October 15, 2014 5:22 PM 
HI
You can add measure:
Net Perm:=CALCULATE(SUM([Perm]);[Grp]="Grp 2")  CALCULATE(SUM([Perm]);[Grp]="Grp 1")
note that ";" in some excel version can be replace by ","
[Grp] column can have different name in your data model, which was not presented
Gordonik
 Proposed as answer by Ed Price  MSFTMicrosoft employee Saturday, October 18, 2014 7:50 AM
 Marked as answer by Michael Amadi Thursday, October 30, 2014 8:58 AM
Thursday, October 16, 2014 8:37 PM