cancel
Showing results for 
Search instead for 
Did you mean: 
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 IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.