Volume- and Price-Variance with multiple unitcosts

[prieper]

Dears,

I am not quite sure, whether there might be a solution for my problem, but there is so much knowledge out there ....

Here we go:

I am supposed to calculate Volume- and Price-variance. For simplicity we may concentrate on the volume-variance only, with formula (Q2-Q1)*P1 [Q2 stands for new quantity, Q1 for old quantity, P1 for the old unitprice]. The volume-effect is supposed to be summarized.

Problem is that I do not have unit-info (thus not unitprice) for certain items.

Rules are:

1. If there are units recorded for the specific item, take them.
2. If there no units recorded for the item, take the sum of all units from this group.
3. If there are no units recorded for the group, take the sum of all units.

So looking at the calculation for T3 for 2016, it will take 500 and divide by the sum of T1 and T2 (=25). Same for 2015. The volume-effect then would calculate (25-22) * 18.18 = 54.54 (~55 shown above).

Needless to say, that number of Item, groups etc is not fixed ...

Is there a way to cover the calculation into one formula, allowing to collapse all dimensions and still showing the above +332?

So far my formula is (works, if all lines have volumes resp. unitprices):

=SUM(AGGR(SUM((Q2-Q1)*P1), Month, City, Item))

Have not managed to solve it as per the above example (think, that there are different granularities to create the sums).

Any ideas?

Edith has attached an example

Edith has replaced the screenshot with a correct one.

Regards

Peter

Message was edited by: Peter attachment

[sunny_talwar]

Hi Peter -

It would be somewhat difficult to provide a complex expression without testing it out a little. Would you be able to provide a sample where you have been working on this? May be we can play around with it a little before we propose anything:)

[sunny_talwar · Accepted solution]

Not sure if this is right because the numbers don't match, but can you check

Expression:

Aggr(

If(SUM(Q2) - SUM(Q1) > 0, (SUM(Q2) - SUM(Q1)) * SUM(C1) / SUM(Q1),

If(Sum(TOTAL <Group> Q2) - Sum(TOTAL <Group> Q1) > 0, (Sum(TOTAL <Group> Q2) - Sum(TOTAL <Group> Q1)) * (Sum(C1)/Sum(TOTAL <Group> Q1)),

(Sum(TOTAL Q2) - Sum(TOTAL Q1)) * (Sum(C1)/Sum(TOTAL Q1))

)), City, Month, Item, Group)

[prieper]

Hi Sunny,

thanks for the feedback.

What needs to be done to have the calculation working, if only 03 or T3 is selected?

Peter

[prieper]

Thanks Sunny for helping towards the correct solution.

Finally have used:

SUM(AGGR(
IF(SUM(Q2) - SUM(Q1) > 0, (SUM(Q2) - SUM(Q1)) * SUM(C1) / SUM(Q1),
IF(SUM({$<Item =, Group = >} TOTAL <Group> Q2) - SUM({$<Item =, Group = >} TOTAL <Group> Q1) > 0, (SUM({$<Item =, Group = >} TOTAL <Group> Q2) - SUM({$<Item =, Group = >} TOTAL <Group> Q1)) * (SUM({$<Item =, Group = >} C1) / SUM({$<Item =, Group = >} TOTAL <Group> Q1)),
(SUM({$<Item =, Group = >} TOTAL Q2) - SUM({$<Item =, Group = >} TOTAL Q1)) * (SUM({$<Item =, Group = >}C1)/SUM({$<Item =, Group = >} TOTAL Q1))
))
, City, Month, Item, Group)
)

Regards Peter

[sunny_talwar]

Awesome, that was going to be my next suggestion. I was not sure if what we have today was correct or not because it did not completely match what you wanted as the result. But I am glad it all worked out at the end.

Best,

Sunny

[prieper]

Hi Sunny,

I made a mistake in the underlying calculation in Excel - sorry. Will correct my initial post.

Peter