Pivot table limiting column expression

[rido1421]

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

[MK_QSL]

use KPI Target  expression as Calculated Dimension..

This has to be something like below..

Aggr(YourKPITargetExpression,SomeDimensionField)

something like..

Aggr(SUM(TOTAL Sales),Customer)

[jonathandienst · Accepted solution]

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.

[rido1421]

Thank you this worked great