Adding total expression to pivot with horizontal dimension

[dafnis14]

Hi,

I have a pivot table with months as horizontal dimension.

I need to add totals expressions with some set analysis, which should appear only at the end (for all months only).

currently they appear for each month.

Attached is the expected report format.

The yellow represents actual value. The grey - forecast values.

Please note that the Total Actual includes  actual month values only.

Would appreciate suggestions how to do it.

Thanks!

[petter]

If you have a sample app with some sample data it would be so much quicker to just show you.

[Gopi_E]

Hi Dafnis,

Can you elaborate exactly what you want to display in your pivot.

[petter]

Here you can see how you can use the [Presentation] tab and enble the [Show Partial Sums] for the dimension [Month] and get the sum for all months on the rightmost column in the pivot table:

[dafnis14]

Hi,

I edited the post. hope it's clearer.

Thanks!

[dafnis14]

Hi,

Please see the update to my post.

Thanks!

[petter]

The answer is almost exactly the same.

You can have an expression like this if the Actual and Forecast are in separate fields:

If( Month > ActualMonth , Sum(Forecast) , Sum(Actual) )

Or if you have the forecast and actual in the same field but have a dimension indicating the type of values:

If( Month > ActualMonth, Sum({<Type={'Forecast'}>} Sales) , Sum( {<Type={'Actual'}>} Sales ) )

[dafnis14]

Hi Petter,

Thanks for helping me...

I don't quite understand your solution.

There is only one field: Month.  (no ActualMonth). I do have a dim for Type.

[dafnis14]

Hi Petter,

I think the way to achieve this is using SecondaryDimensionality.

The second dimension... or how to use secondarydimensionality()

if(SecondaryDimensionality() =0, Sum( {<Type={'Actual'}>} Sales ) , Sum(Sales )

Thanks for giving me the direction...

[petter]

yes that should work too...