cancel
Showing results for
Did you mean:
Helper I

## SUMX WITH CONDITION BASED ON MEASURE

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]))
6 REPLIES 6
Super User IV

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

Proud to be a Super User!

Helper I

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

Community Support

@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.
Helper I

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].

Community Support

@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

Helper I

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

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

Announcements