1 2 Previous Next 26 Replies Latest reply on Mar 21, 2017 7:05 PM by Morgan DUARTE

# Delta Price calculation

Hi there,

I'm trying to calculate a Delta Price. (Difference between this year average selling price and last year average selling price, which is then multiplied by the quantity of this year)

I'm actually have difficulties to do such calculation.

To get the Average selling price (ASP) it's okay, using Total(sum[Sales]) / (Total(sum([QTY]), then have it run at "Cell" level, but then to run the difference and multiplied by quantity..I don't know how to do it.

Here is attached an example.

Ideally, I'd like to do a graph later on, with only the Delta Price value (Positive or negative) with a vertical axis and horizontal bars with the deviation, for each different offer.

Hope you can help !

Thank you !

• ###### 1. Re: Delta Price calculation

Hi Morgan,

Please refer the below screenshot and confirm the output. Attaching the twbx file for the same. (9.3 version)

Calc name - ASP Delta

ZN({ FIXED [Activity], [Product], [Year] : AVG(IF [Year]='2016' THEN ([Selling Price])/([QTY]) END) } )

-zn({ FIXED [Activity], [Product], [Year] : AVG(IF [Year]='2015' THEN ([Selling Price])/([QTY]) END) })

Supporting calcs to get the above view.

Calc Name - AVG SP 2015

ZN({ FIXED [Activity], [Product], [Year] : AVG(IF [Year]='2015' THEN ([Selling Price])/([QTY]) END) } )

Calc Name - AVG SP 2016

ZN({ FIXED [Activity], [Product], [Year] : AVG(IF [Year]='2016' THEN ([Selling Price])/([QTY]) END) } )

Let me know if this helps.

Thanks and Regards,

Ashish Chaudhari

1 of 1 people found this helpful
• ###### 2. Re: Delta Price calculation

Is that what you're looking for?

Workbook (version 9.3) attached for your reference. Feel free to ask If you've any query.

Mahfooj

2 of 2 people found this helpful
• ###### 3. Re: Delta Price calculation

I works but I have to remove the year then, however, I want to keep the results of 2016 instead of having the sum of 2015 and 2016 then.

Is it doable?

Thank you

• ###### 4. Re: Delta Price calculation

Yes it is MAHFOOJ KHAN!

What if I want to do the same but at Activity level. (Sum of product), without showing products?

Thanks !

• ###### 5. Re: Delta Price calculation

Hi Morgan,

do you mean something like this?

Let me know.

-Ashish

• ###### 6. Re: Delta Price calculation

Measures are compute by Activity only. If you don't want products then you can hide the headers like this.

Or do you want this? You have to just remove the product dimension from column shelf that's it.

Let me know If you've any query.

• ###### 7. Re: Delta Price calculation

@MAHFOOJ KHAN

I would indeed also need the total at activity level.

But the calculation does not seem to be correct then. For Desktop, the total DP is -50, not +20.74

Any idea how to fix this?

@ASHISH CHAUDHARI

I'm looking for 2016 Sales and 2016 QTY in fact. But I suppose I can do a similar formula than AVG SP 2016.

I will try this

• ###### 8. Re: Delta Price calculation

Hi Morgan,

Please find the attached workbook where I have calculated Sales and Qty for 2016 along with delta. Please find the updated workbook.

Thanks and Regards,

Ashish Chaudhari

1 of 1 people found this helpful
• ###### 9. Re: Delta Price calculation

Thanks.

Weird thing, the AVG SP does not provide a correct result on my actual data...

Sometimes it does, sometimes not...

Here are 2 examples

Any idea why?

Thank you

• ###### 10. Re: Delta Price calculation

Hi Morgan,

This is bit strange. Try excluding Null from the view. As I am trying to calculate the 2016 ASP is giving proper results for NULL but not for Ecobuilding.

Also tell me the sales and qty number for 2015 so that I can try checking the stuff

-Ashish

• ###### 11. Re: Delta Price calculation

I've excluded null vales from the value and the QTY, but still the same.

For 2015, on the ref 30T, value = 298,862,695, with QTY 8,283

I did not figure it out yet.

• ###### 12. Re: Delta Price calculation

Mark, Any suggestions on this? On Sample this is working absolutely fine but when implementing this stuff isn't giving right output.

-Ashish

• ###### 13. Re: Delta Price calculation

Any idea why?

Hi Morgan

Disclaimer - i have only skim read the thread, and picking out this particular issue...

I wondered if the Qty (and maybe others), should be SUM([Qty]), and that you may be divided by a lower level of aggregation than you expect.

It would explain why some work, and others dont. The ones which work only have 1 record, where the ones that dont have >1 records

Just a guess

I'll try and have a more detailed look - but can't promise anything!

Cheers

Mark

1 of 1 people found this helpful
• ###### 14. Re: Delta Price calculation

Checking the sample, my theory/ guess holds, each 2016 record has only 1 'record', so for now the aggregation wouldn't matter but scale it up (i.e. add more records for each type/year combination), and it could become a problem...

1 of 1 people found this helpful
1 2 Previous Next