[gdesiletParticipant Started: 0Replies: 3Total: 3]

I wrote this as my compare and it seems work but I would have to write it for all the P&L items.  Is there a formula that would allow me to just subtract one version against another so no matter what “fact” I pull in a get the compare? I’m new to the calculate measure, thanks for the help

=CALCULATE([units],’Version'[version]=”Budget”)-CALCULATE([units],’Version'[version]=”Actuals”)

 

Attachments:

You must be logged in to view attached files.

[gdesiletParticipant Started: 0Replies: 3Total: 3]

Thanks Tom,

so if I wanted to write a calculate function to show me the difference  between Actuals over budget, would I have to write the function for each item of a P&L?  meaning would there need to be one for Units, another for Net Revenue, etc…   OR could I write the calculate compare based on the different versions (actuals, budget….)?

thanks for the help.  I was looking through Rob and Avi’s book but couldn’t find the answer.

[gdesiletParticipant Started: 0Replies: 3Total: 3]

I have a slightly similar question regarding budgets/ actuals/ and “Flash” which is a adjusted forecast based on Actuals and projected budget.  All my data comes from one database but management likes to look at the data in different ways.  I’ve put all the data into PowerPivot living in one fact table but I want to be able to write some calculate functions that compare Actuals to budget, or year over year, or flash to budget.

Do the different versions (Budget, Actuals, Flash..) each need to live in their own fact table or can they all be in the same one and still be able to write the compare functions?

thanks,

Greg

[Author]

Posts