Cannot Get a Measure to Sum As Expected

[Anonymous]

Hello everyone, and thanks in advance for any and all help you can provide.  It is very much appreciated!

I've been pulling my hair out all day on this one.  I've tried to research the question, and saw some on-line resources that referenced including HASON Filters and some other fixes.  However I can't seem to get anything to work correctly.

Summary:

I'm trying to calculate Year-Over-Year Price Increases per Product # and then be able to aggregate this up for a SalesPerson and Region.  I am trying to exclude any Product # that has not had any Sales in the Previous year.

Background:

I have a Sales Table, called (order_history), as well as a Date Table called (calendardatetable).  I have a large number of Measures built off of these tables, with the pertinent ones being the following and the last one in Red that's giving me the problem: 

• Revenue Measures
  • Extended Revenue CY (Calendar) = CALCULATE(SUM(order_history[ext_price]))
  • Extended Revenue PY (Calendar) = CALCULATE(SUM(order_history[ext_price]),SAMEPERIODLASTYEAR(calendardatetable[Date]))
• Price Measures
  • Average Price = [Extended Revenue CY (Calendar)]/[Quantity CY (Calendar)]
  • Average Price PY (Calendar) = CALCULATE([Extended Revenue CY (Calendar)]/[Quantity CY (Calendar)],SAMEPERIODLASTYEAR(calendardatetable[Date]))
  • YoY Average Price = [Average Price] - [Average Price PY (Calendar)]
• Quantity Measures 
  • Quantity CY (Calendar) = CALCULATE(SUM(order_hstory[shp_qty]))
  • Quantity PY (Calendar) = CALCULATE(SUM(order_history[shp_qty]),SAMEPERIODLASTYEAR(calendardatetable[date]))
• YoY Price Differential
  • CY Qty Extended YoY Price Differential = if([Quantity PY (Calendar)]>0,[YoY Average Price]*[Quantity CY (Calendar)],0)

So basically, what I'm trying to do is Calculate the Price Differential on items that were sold in the Current Year and in the Prior Year, and apply that YoY Average Price to the Current Year Quantity Sold.  I don't want to include in the calculation items that were sold in the current year but not last year (which would yield a large Positive number) or those that were sold last year but with no Sales in the current year (which would yield a large Negative number).

So I'm sure that the experts out there already can guess what is happening: my DAX formula is accurately being applied to both the Rows (which I want it to do) and the total (which I don't want it to do).  In the screenshot below, it's the 4th column over titled [CY Qty Extended YoY Price Differential] that's the problem.  I need this Total to show -$3,319.22, not the $51,223.40 that it is currently calculating independently of the row results.

Please help!

[amitchandak · Accepted solution]

@Anonymous , Try the formula in one of the 2 ways

CY Qty Extended YoY Price Differential = sumx(values(customer[Customer desc]), if(not(isblank([Quantity PY (Calendar)])) && [Quantity PY (Calendar)]>0,[YoY Average Price]*[Quantity CY (Calendar)],0))

CY Qty Extended YoY Price Differential = sumx(addcolumns(Summarize(customer[Customer desc], Table[Year]),"_1", if(not(isblank([Quantity PY (Calendar)])) && [Quantity PY (Calendar)]>0,[YoY Average Price]*[Quantity CY (Calendar)],0)),[_1])

[Anonymous]

Thanks so much for pointing me in the right direction @amitchandak , it is very much appreciated!

So what I was able to get to work was a slight derivation of your second suggestion above, which was:

CY Qty Extended YoY Price Differential = SUMX(

VALUES(

order_history[product]),

IF(NOT(ISBLANK([Quantity PY (Calendar)])

)

&&

[Quantity PY (Calendar)]>0,[YoY Average Price]*[Quantity CY (Calendar)],0

)

)

I never would have gotten that without your input.  Thanks again!

[v-robertq-msft]

Hi, @Anonymous 

Glad to hear that you have solved your problem by the reply of amitchandak, would you like to mark his reply as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.