Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
brhr
Helper I
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
amitchandak
Super User
Super User

@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_)

 

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.