Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a Pivot Table like Dimension1,Dimension2,Dimension3,Expression1,Expression2,Expression3,Expression4
I need To insert a Partial Sum of the Esxpression Columns only for the Expression2 and Expression3.
I use a function "Show Partial Sums" in a Presentation Tab of the Pivot Table Propresties but this function give me a Total in all Expression Columns
Is There a solution for this?
Many thanks and bst regards
Look into the function dimensionality(). The Partial Sum row is going to have a different then the data so you could put an if(dimensionality = 0, null(), sum(Sales)) in your expression. All total rows should have dimensionality() = 0.
Regards.
Many thanks for the answer!sorry but i don't understand where i should put the condition that you worte because in a Pivot Table in the "Expressions" Tab the "Expression Total" in "Total Mode" Box is lock.
Many thanks and best Regards
Put the formula in the Definition field in the Expressions tab for the expression that you don't want to show a partial sum.
if(dimensionality() =0,null(),sum(Sales))
Instead of sum(Sales), put the expression that you are using.
Regards.
Hi,
Thanks a Lot for the answer but seems dosn't work.
I attach you an example(without sense 😄 ) like my report.I need to have a partial sum only for the Expression Column Data Acutal and not for the Expression Column Year.
Thanks
Karl's answer is the correct one. Using an If with Dimensionality will get you what you want.
Also, Dimensionality() can return different values in a pivot with multiple partial sums. The highest level total will be zero, where other levels will be higher. In your example, 3 is your normal data level (add Dimensionality() as an expression to find this out). So:
=If(Dimensionality() <3, ' ', Sum(Year))
I've attached a sample.
Oh yeah!Yes I'm not understand the Karl's answer.sorry but i'm not very able to use these function.
Many thanks for the help!!!