5 Replies Latest reply: Sep 17, 2018 2:38 AM by Andrey Krylov

how to Maintain static total & sub total value in pivot table irrespective of the selection

I have data like this and if i load this to pivot table

Dim1,     Dim2,     Dim3,     Fact1,     Fact2

A,               AA,     AAA ,     10,          20

A,               AB,     AC,         6,           12

A,               AB,     AD,         5,           12

B,               BA,      BB,        2,           4

if we select Dim1=A and Dim2=AB then total for Dim1,Fact1 should be 21. and Dim2, Fact1 total should be- 11

Required Output:

Dim1Dim2Dim3Fact1Fact2
A total--2144
AAB Total -1124
AABAC612

Any idea how to achieve this static values for total and subtotal in pivot table? And it should display only selected dimensions. In this case it should show only A under Dim1, not B.

• Re: how to Maintain static total & sub total value in pivot table irrespective of the selection

Hi, Shwetha. I think dimensionality() is what you need. And expression is like this

If(dimensionality() = 1, Sum({< Dim2, Dim3 >} Fact1),

If(dimensionality() = 2, Sum({< Dim1, Dim3 >} Fact1),

Sum(Fact1)))

• Re: how to Maintain static total & sub total value in pivot table irrespective of the selection

I tried this, But Problem is when i select particular dimension Pivot table display for all values in that dimension .

Only for select dimension value it shows fact value and for remaining it is 0.

Any way to display selected value?

Like if i select AB in Dim and AC in Dim3 then it should display 1 row and total for Dim1 is 21 and Dim2 is 11 for Fact1

• Re: how to Maintain static total & sub total value in pivot table irrespective of the selection

Thanks for solution can you please help me to change pivot Dimension Values based on selection.

• Re: how to Maintain static total & sub total value in pivot table irrespective of the selection

Can you share a sample, Shwetha?

• Re: how to Maintain static total & sub total value in pivot table irrespective of the selection

Hi,