Custom Hierarchy with a Baseline
In my analysis, I work with both a baseline and an ongoing change between the most recent statement and the previous statement at any given time. It is relatively easy to calculate the ongoing changes with the syntax sum (0, 0, m1) - sum (0, -3, m1, 0) - new measure. The problem emerges when I have to multiply the calculated changes by a baseline value in a custom hierarchy.
I have tried to make a another calculation (new measure) on the basis of this syntax: sum(0, d1, m3). I have tried with different levels ect. but without resolving the problem.
The blue markings in the calculated measure must be multiplied by the value marked blue. The same applies for the other colored selections. The values in "31-12-2019" is my Baseline!
Anyone knows how to fix this?
Comments
Hi John
Im not sure I understand your question correctly, but I try to answer anyway.
If I understand you correct, you need to get the first three rows of "Gennemsnit pris" copied down in a new column. And then you can multiply it with your column "Mængde Ændring Hjælp".
I would first do a new column that always count to 3 all the way down in the column (or more precise it counts 1, 2, 0, 1, 2, 0, 1, 2, 0 etc.). You can do this with the syntax: mod(allcount(d1, d1:0, m1);3)
Then I would make a new column with your baseline using the syntax:
if sum(c1, 0, m1) = 1 then sum(0, d1, m3) else if sum(c1, 0, m1) = 2 then sum(0, d2, m3) else if sum(c1, 0, m1) = 0 then sum(0, d3, m3) else 0
where (c1,0,m1) is refering to the column above.
And the at last you can easy multiply the lastest column with your "Mængde Ændring Hjælp" directly.
I hope it makes sence?
See my example below:
Hi, Louise
You understood it correctly. It works very nice.
I had to make small changes to the syntax in order to get the result that I'm looking for.
Thanks a lot for the help.
Please sign in to leave a comment.