Pivot Table data representation

[mhmhmh91]

Hi,

I have a question about Pivot table.

I have two tables like below:

Table A:

ItemID	Group	Item Value
a100	A	100
b100	A	110
c100	B	120
d100	C	130

Table B:

Group	Group Value
A	300
B	500
C	200

The relationship between Item and Group is like Child and Parent, but Item Value and Group Value are separate and has not relationship with each other. I now want a pivot table like below (under Group Value column, leave the cell blank instead of display Group Value of the Group):

Group / Item	Item Value	Group Value
A	210	300
a100	100	-
b100	110	-
B	120	500
c100	120	-
C	130	200
d100	130	-

[rahulpawarb · Accepted solution]

Hello Mandi,

Trust that you are doing good!

Please follow below steps to get the desired result:

1. Add Group & ItemID in used dimension list of Pivot table.

2. In Expression tab add below expressions

//Item Value
Sum([Item Value])
//Group Value
If(Dimensionality()=1, Sum([Group Value]), Null())

* Here we are making use of Dimensionality() function to check whether row is of type detail or summary and based on it we are populating the expression value.

3. Locate the Style tab of Pivot table chart object and select Indent Mode & Use Only First Dimension Label options (While doing this provide Group / Item as label to Group dimension on Dimension tab)

Also refer the sample application attached herewith.

Hope this will be helpful.

Regards!

Rahul

[mhmhmh91]

It works perfectly. Thank you, Rahul!

Best,

Mandi

[rahulpawarb]

Cheers,

Rahul