How do I add a calculated variance to an OLAP cube driven pivot table report?

(0) Share

Report

Posted on by Rick Fitzgibbons

Using the GL cube out of the box from AX 2012, I'm trying to compare actuals to budget, but there is no calculated variance for this already in the cube. I don't want to add the calculation outside the pivot table. Rather, I'd like to add a calculated field but with OLAP datasources it's not possible. I understand MDX can do this but the Help is not getting me there.

Has anyone done this using Pivot Table Tools>>Options>>Field, Items & Sets>>Create Sets.... ?

I've also tried to use the field settings and 'show values as... difference from' to create a comparison/difference amount but that too has not worked for me.

Has anyone been successful creating a variance within a table using the field setting, 'show value as'?

*This post is locked for comments

I have the same question (0)

All responses (1)

Answers (0)

Lance [MSFT] on at

Like (0)

Report

There should be a kpi defined in that cube for Revenue budget variance and another for Expense budget variance. If those aren't doing what you want, perhap you could re-use the mdx statements that define those kpi to create your calculation?

Was this reply helpful? Yes No