Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
@brhr , what is that you trying to get using this formula ?
To get a calculation for a volume effect - meaning controlling for price changes at customer and product level.
@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_)
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].
@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
[Avg Sales Price] = DIVIDE ([Sales], [Quantity])
[Avg Sales Price LY) = CALCULATE([Avg Sales Price], SAMEPERIODLASTYEAR('Time'[Date]))
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |