Skip to Content
0
Feb 01, 2023 at 02:41 PM

Best Practice for Complex calculation in Measures (or accounts) SAC

292 Views

Hello

I have built a model that I use for actual analysis and budget planning.

I trying to avoid data actions and endless number of measures. I have some difficulties when I try to use the same measure "Local Value" for few purposes (calculating income and rebates). see below.

image.png

by adding a measure for "actual upload" (ACT_LOC_VALUE) I could easily wrote the below however that answers the Revenue account only. I've tried to combine more calculation in this condition using ACCOUNTLOOKUP - I didn't do it well.

IF([d/Version]!= "public.Actual" ,[LOC_PRC]*[QUANTITY] ,[ACT_LOC_VALUE] )

When I tried to write formulas in the account dimension (using MEASURELOOKUP) I was not able to add the condition "if version ISN'T actual, execute the formula, if version IS actual fill with the ETL upload" - is there anyway to write it?

image.png

this code on my account dimension could work if I was able to write down that I want this calculation to be applied on my "local value" measure only? can I do that?

MEASURELOOKUP([70701000],[LOC_PRC])*MEASURELOOKUP([70701000],[QUANTITY])

It's looks like I miss something fundamental and I would appreciate your help.

Deb

Attachments

image.png (93.7 kB)
image.png (32.3 kB)