Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
use KPI Target expression as Calculated Dimension..
This has to be something like below..
Aggr(YourKPITargetExpression,SomeDimensionField)
something like..
Aggr(SUM(TOTAL Sales),Customer)
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.
Thank you this worked great