locked
How to add new column in PivotTable. RRS feed

  • 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

    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]

    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]

    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

    Thursday, October 16, 2014 8:37 PM