SUMX over indirectly related virtual tables

[brhr]

Hi there,

I am struggling with creating a price volume mix measure.

I am doing a SUMX over two tables, that are used as filters for the major one where sales and quantity is placed.

My current measure is:

Volume Effect (Sales Order) =

VAR SummarizedTable = SUMMARIZE('Sales Order Details',Customer[Customer ID],'Product'[Product ID])

return

SUMX(SummarizedTable,IF([Avg Sales Price LY]=0,[Avg Sales Price],[Avg Sales Price LY])*[Quantity Δ])

The measure is correct for most values, however, when Quantity is 0 (Quantity is inserted in the measure [Quantity Δ]), the formula does not work. 

Any ideas how to solve it?

[FarhanAhmed]

What is [Quantity Δ] ?

Can you please share the formula ?

Also put this value in your Table and sshare the screenshot 

[brhr]

Quantity Δ is Quantity - Quantity LY. Below you have a few examples of rows where the formula works and where it doesn't...

The weird part is that Price Effect uses a similar measure format with SUMX(SUMMARIZE and returns the correct figures for all rows. 

Price Effect (Sales Order) = SUMX(

SUMMARIZE('Sales Order Details','Customer'[Customer ID],'Product'[Product ID]),

IF(OR([Avg Sales Price]=0,[Avg Sales Price LY]=0),BLANK(),

    ([Avg Sales Price]-[Avg Sales Price LY])*[Quantity]))

[FarhanAhmed]

can you please create a new measure a for this statement and see what is returning ?

Looks as if this statement is not evaluating correctly which is causing Blank values for Volume Eff

Test = IF([Avg Sales Price LY]=0,[Avg Sales Price],[Avg Sales Price LY])

[brhr]

It is working well... 😕

[FarhanAhmed]

One more question why are you Summarizing your table ?

Isn't below formula give you your desired results ?

IF([Avg Sales Price LY]=0,[Avg Sales Price],[Avg Sales Price LY])*[Quantity Δ]

[brhr]

To calculate the effect of price and volume changes you need to assess at lowest level possible - in this case Customer and Product.

If I don't SUMMARIZE, then it will only look at the aggregate level and the measure will be incorrect. 

For example, the Avg. Sales Price for country level might not be 0 for a certain product, but if you look by customer, then you will see that some customers with Avg. Sales Price = 0 and those need to be assessed accordingly. 

The formula you suggested will not address this issue and the totals will be incorrect. If you sum the column Volume Effect (No summarize), the total should be -8.162 and not -23.230 as displayed.

[amitchandak]

@brhr , Try like this

SUMMARIZE('Sales Order Details',Customer[Customer ID],'Product'[Product ID] ,"_1", [Avg Sales Price LY],"_2",[Avg Sales Price] ,"_3",[Quantity Δ] )
return
SUMX(SummarizedTable,IF([_1]=0,[_2],[_1])*[_3])

[brhr]

Yes, I tried that before with no success. 

The results are the same. As you can see below, the challenge is that when quantity is blank/zero, the calculation does not work.

If the calculation was working, the Volume Effect below should be (0-20)*19,47 = -389,4.

However, nothing shows. It is like it doesn't recognize the quantity when it is blank/zero 😞