Variance Analysis (Price, Volume, Mix) with 3 dimension tables - get Volume measure Correct

[Anonymous]

Hello all,

I am building a variance analysis (Price-Volume-Mix-otherA-otherB Effects breakdown) with referencing 3 dimension tables:

- dDate: Calendar table (Month from the picture below)

- dCustomer: Customer table (Customer name from the picture below)

- dProduct: Product table (Product Brand, Product Packaging, Product Name from the picture below)

and the fact table fSales

The objective is to build dynamic measures for each of the P-V-M effects, and it will add up according to what dimensions are applied. I am having issue with the Volume Effect.

Picture below snapshotted the desired outcome for the Volume Effect (last column):

The requirement for the Volume Effect is as follow:

1a. If product is new (i.e. Volume_LY = 0) -> Volume Effect = Volume_TY * Price_TY

1b. otherwise (i.e. existing products or delisted products) -> Volume Effect = (Volume_TY - Volume_LY) * Price LY

2. The effects sums up at sub-total or grand total level of each hierarchy

Here is the DAX I used to create the calcluated measure for the Volume Effect:

Volume_Effect :=
SUMX (
    SUMMARIZE ( fSales, dCustomer[Customer Name], dProduct[Product Name] ),
    IF (
        [Volume_LY] = 0,
        [Volume_TY] * [Price_TY],
        [Volume_TY - Volume_LY] * [Price_LY]
    )
)

However the problem is, the DAX doesn't work for delisted products (i.e. Volume_TY = 0). (works perfectly fine for new or existing products)

Such as Product 4 as follow:

And hence also affecting all the sub-totals in the parent hierachies.

Appreacite if anyone can help with the situation. Thanks a lot!

The calculated measure has to satisfy all of the 3 below:

1. Summation of the Volume Effect at parent total
(i.e. sum of product 1, 2..., n Volume effect = pack A's Volume effect; sum of pack A, B..., n Volume effect = Brand A's effect; and so on)

2. The Volume Effect is dynamic on all dDate, dCustomer, dProduct tables.

3. It cannot be a balancing effect (Revenue difference - sum of the rest Effects), since there is another effect treated as balancing effect already.

Thanks in advance once again!

[parry2k]

@Anonymous i think the issue in the highlighted rows is with the [Volume TY] because it is blank.

[Anonymous]

Thank you. Any thoughts on how to make the DAX works in this situation? Much appreciated!

[Bacchus]

Hey, did you find a solution yourself in the meantime?

If you don't, you can send me your PowerBI file and I could have a look.