Version variance as calculated measure at the model level

[ekov514]

Hello Community, 

It appears that SAC won’t allow us to create model based calculated measures  that compare actuals to budget version for example. To be specific, it would, but you get a zero or the total value of only one version because the measure can’t access the other version. Would there be any past discussions or documentation out there that can explain this limitation?  Sounds to me like a case for an influence ticket, but I'd like to share any toughts of this matter before submitting it. 

I do know how to get it done in a story, but there are reasons why that may not the best solution: 

A) Comments. I’d like to keep my variance comments at the model level and not incur the risk of losing them when someone, by mistake, updates the name of a story measure. Keeping all the comments only on regular measures is not always practical due to quantity of information that will need to be reviewed before you get to the section you need. 

B) Let’s think about an organization trying to implement SAC for Planning and would like to involve as many users as possible to use SAC (self-service to some extent), exposing users to standardized pre-built measures, can help with the adoption of the tool. 

P.s. I know I can set my Budget version as actual category, but that sounds like using the system incorrectly and no documentation provided as to what can be a limitation of doing that. 

Thank you! 

[YaoYao · Accepted solution]

Hi! Have you try to use the "lookup" function in the formula editor of the modeler to create a calculation comparison between different versions?

1. first create a measure

2. in the formula editor type the following formula: in this case, i calculate the "gross sales completion rate" by divide the gross sales of the actual version by the gross sales of the budget version. Of course you can use subtraction or other formula according to your needs.
LOOKUP([GROSS_SALES],[d/Version]="public.Actual")/LOOKUP([GROSS_SALES],[d/Version]="public.Budget")

3. save this measure. then you can find it in your story and use it everywhere.

[MKreitlein · Accepted solution]

Great idea! I was looking for a way of "absolute Delta" or "Delta in %" between two versions some years ago... but did not have the idea to do it this way 🙂

[ekov514 · Accepted solution]

yes, this is exactly what can't be done. You will either get a zero or div/zero error.