3 Replies Latest reply: Jan 19, 2016 4:54 AM by Ridhaa Hendricks

# Pivot table limiting column expression

Hi There

I have a pivot table

in my Pivot table I have two expressions

Sum(Actuals)- I would like to see this by month

Sum(KPI) -I only want to see this column once, as this is the KPi for the Year.(This is a very complex expression and I cannot use it as a dimension)

 Jan Feb Mar KPI Target Brand 1000 1200 1100 1500

any Idea how I can achive this.

This is what I am currently getting:

 Jan KPI Target Feb KPI Target Mar KPI Target Brand 1000 1500 1200 1500 1100 1500

Your assistance is appreciated.

Thanks

• ###### Re: Pivot table limiting column expression

use KPI Target  expression as Calculated Dimension..

This has to be something like below..

Aggr(YourKPITargetExpression,SomeDimensionField)

something like..

Aggr(SUM(TOTAL Sales),Customer)

• ###### Re: Pivot table limiting column expression

If you use Dimensionality() -- or SecondaryDimensionality() for rows -- you can create a custom total expression which you could use for your KPI value.

Set up a pivot table with Month as the dimension and Sum(Actuals) as the expression. Enable partial totals for the expressions (on the Presentation tab) and pivot the table so the Months are columns. Now modify the expression:

=If(SecondaryDimensionality() = 0, Sum(KPI), Sum(Actuals))

Finally, change the "Label for Totals" to read "KPI Target" (or whatever you want it to read).

If done correctly, that should result in the monthly amounts and the KPI value in the 'total' column labelled with the label you entered.

• ###### Re: Pivot table limiting column expression

Thank you this worked great