get Volume measure Correct - Variance analysis with parents hierarchies

[Anonymous]

Example file

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)

- dSKU: 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.

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, fSales[Customer Name], fSales[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). 

The issue seems to be for delisted items have blank records under "TY", hence the DAX cannot capture the corresponding row. How do I fix the DAX to "look into" sales data for both years?

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.

Attached the sample file for your reference.

Thanks in advance once again!

[avanderschilden]

Hello,

Did you try to use the ISBLANK() function to replace blank values in your calculation with zero's?

for example;

IF(ISBLANK(Value),0,(Value))

Regards,

Adrian

[Anonymous]

Thanks Adrian. I've tried but still not working.

[avanderschilden]

Do you mind sharing the pbix file?

[Anonymous]

Hi Adrian,

I'm building all my DAX measures on Powerpivot, and then live connect to PowerBI. 

Please find the sample file here: Example file

You will find the DAX measures under Data ->> Data Model / Powerpivot.

Thanks once again!

[avanderschilden]

Something like this?

[Anonymous]

Hi, it's showing all the products now.

However the Volume Effect (Volume_TY - Volume_LY) * Price_LY is still not showing properly.

For instance, Customer 1's SKU 13 shall be showing Volume effect = -$52,785.

[avanderschilden]

What about this?

[Anonymous]

Hi @avanderschilden , yes! Column Copy of Volume Effect looks exactly what I needed. Appreciate if you may share your approach on the solution.

[avanderschilden]

Hello,

Here is the link to download the file;

https://we.tl/t-8zCzzGjzSl

Regards,

Adrian

[Anonymous]

Hello Adrian,

Thanks again for your help. Sorry but just realized the sub-totals are not summing up the Volume Effect properly.

For instance from picture below: Under Customer 3, Brand A Total's Volume Effect shall be -$274,126 (Multi bottle Total + Multi Can Total + Single bottle Total + Single Can Total), but not -$100,265. Hence I tried to implied SUMX at the beginning of the formula. 

Thanks once again!

[avanderschilden]

What about this?

https://we.tl/t-LKx0zX6AXe

[Anonymous]

Hi @avanderschilden ,

Thanks once again. However still two issues remained unsovled:

i) The sub-total on Pack Types are missing (e.g. row Multi bottle Total)

ii) the sub-total on the Customer level wasn't adding up properly. The Volume Effect shall be dynamic on all dDate, dCustomer, dProduct tables. E.g. For picture below, the Grand Total shall be adding up all Customer 1, 2, 3's sub-totals and showing -$3,065, 328

I've revised your formula as follow using a nested SUMX

Volume Effect :=
SUMX ( dCustomer, 
	SUMX ( dSKU, 
	( [Volume_TY] - [Volume_LY] ) * [Price_TY] 
	) 
)

This solves issue (ii) the dynamic summation of sub-totals. However issue (i) is yet not addressed. 

Another downside of using nested SUMX can also lead to poor performance issue since this might not be the most efficient DAX measure (the actual dataset I'm using can easily goes beyond million rows of data.

Any thoughts? Thanks once again!!

[Anonymous]

Hello, any thoughts?