dynamic variance calc, local member formuals

[Former Member]

How can we make the variance calculation columns dynamic and not use epmposition to calculate the variance?  When the data range changes, the formulas get messed up. Report:  Row Axis - Accounts Column Axis - Two dimensions - Category and Cost Center Category values Actual and Budget in column axis of the report can be changed by users Cost center values is in column axis of the report can be changed by the users Data range in the report will vary depending upon the cost center dimension that the users choose Attached an example of the report. Tried using EPM comparison, but couldn't make it to work. Any help on the formula to be used as well as syntax would be helpful Thanks

[former_member186338]

Please provide a screenshot of the required report with variance columns.

[Former Member]

Hi Vadim, Here you go, original thread has it as an attachment.  Attached again to this response,

[former_member186338]

With everything variable (category and costcenter) you will have to rearrange your report like JP recommended.

Then use simple formula like =D10-E10 with Use Excel Cell reference. Member to be inserted after category dimension.

[former_member186338]

P.S. No way to have a dynamic number of columns after report...

[former_member186338]

Like here:

[Former Member]

Hi Al,

How do you want the variance calculation? Can you give an example...Or are the columns H,I,J the local members required?

Regards,

JP

[Former Member]

Can you modify the report a bit? Shift Costcenter above and have category dimension below???

Then you can have local member after every costcenter with the variance calculation like this...even if the cost center increase the formula follows.

Local member formula is =EPMMEMBER([CATEGORY].[PARENTH1].[BUDGET])-EPMMEMBER([CATEGORY].[PARENTH1].[ACTUAL]) and attach it to member Actual in my case...

This approach is applicable only if you can modify the design of your report.

Regards,

JP

[Former Member]

Hi JP, It was helpful, but the need is that categories need to be dynamic too.  Similar to cost centers need to be dynamic, categories need to be dynamic too.  Users may choose Actual and Budget or  Forecast and Budget or Forecast and Plan etc.  Any insight would be helpful.