SUMX WITH CONDITION BASED ON MEASURE

[brhr]

Hi there,

I am struggling with a measure. It seems like the SUMX does not accept conditions based on a calculated measure.

Hence, my challenge is on the conditional part of the SUMX.

Has anyone experienced the same? 

Any suggestions on how to solve it?

Volume Effect =

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

RETURN SUMX(Tab, IF(OR([Avg Sales Price]=0,[Avg Sales Price LY]=0),[Sales Growth],

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

[amitchandak]

@brhr , what is that you trying to get using this formula ?

[brhr]

To get a calculation for a volume effect - meaning controlling for price changes at customer and product level.

[V-pazhen-msft]

@brhr 
I guess the problem is the if_ expression try this:

Volume Effect =
VAR Tab = SUMMARIZE('Sales Order Details',Customer[Customer ID],'Product'[Product ID])
Var if_= IF([Avg Sales Price]=0 || [Avg Sales Price LY]=0,[Sales Growth], ([Quantity]-[Quantity LY])*[Avg Sales Price LY])

RETURN SUMX(Tab, if_)

BTW, if the the refrened fields e,g. if [quantity] is a column, you might need to add MAX() in front to MAX(quantity), if those measures ignore this.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[brhr]

Yes, I figured the problem is on the IF expression. However, your solution does not work either. It is like the condition is not evaluated at all...

Regardless if the [Avg Sales Price] or the [Avg Sales Price LY] are 0 or blank, it does not accept the condition and returns me 0 instead of [Sales Growth].

[V-pazhen-msft]

@brhr 

You should look at the  [Avg Sales Price] and [Avg Sales Price LY] measures, can you show us the formulas for the 2 measures. 

Paul

[brhr]

[Avg Sales Price] = DIVIDE ([Sales], [Quantity])

[Avg Sales Price LY) = CALCULATE([Avg Sales Price], SAMEPERIODLASTYEAR('Time'[Date]))