1 2 Previous Next 26 Replies Latest reply on Mar 21, 2017 7:05 PM by Morgan DUARTE Go to original post
• ###### 15. Re: Delta Price calculation

Hi Mark and all,

It's probably the issue indeed.

Here is a revised example with multiple records and the result is not correct indeed.

The correct result for X450 should be 127.27, not 129 as ASP 2016

To solve this, I could create a calculated ASP 2016:

ZN({ FIXED [Reporting 1], [Material], [Year] : sum([2016 Orders])/sum([2016 QTY])})

But then if I want to calculate the Delta Price which should be:

([ASP 2016]-[ASP 2015])*[2016 QTY)

Provides wrong results. (ASP 2016 - ASP 2015) is correct, but when doing the multiplication, it's not.

It's probably a matter of level of aggregation.

Any idea how to fix it?

Thank you !

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

Hi Morgan,

That's really great insight from Mark, as I was not even aware of there were multiple rows for each of the product. His this insight has helped me to build below calc. Kindly update the calc.

AVG SP 2016

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

AVG SP 2015

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

Note : I will attach the screenshot and workbook in other post as things get stuck for administration. You will see it once moderator approves them. I have got the output as stated by you. This was good learning.

Thanks and Regards,

Ashish Chaudhari

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

Hi Morgan,

Please refer to the below screenshot and confirm the output. I have used calculations that I have pasted above. Also find the twbx attached to this post for your reference. Thanks for the insight of multiple rows Mark

Thanks and Regards,

Ashish Chaudhari

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

Great !

It works now !

So I'm calculating the Delta Price as ([ASP 2016]-[ASP 2015])*[QTY 2016]. It also works, but if I add a total calculation, the result is correct at the Activity level. And if I remove [Product] from the view, then, it's incorrect.

Any idea how to fix this?

Thank you !

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

Hi Morgan,

Try this code

AVG SP 2016

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

Thanks and Regards,

Ashish Chaudhari

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

Thanks a lot !

But the results is not as expected.

Let's tale the Activity Laptop, the Delta Price at Product level is correct, 1,590 for X300 and 4,842 for X450.

So the total Delta Price for Activity should be 6,432 (1,590+4,842)

But even with your revised formula, I don't find this result

Here is the revised Export with the calculation

Thank you !

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

Hi Morgan,

Are you looking for this output?

-Ashish

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

I don't think the value is correct then. If you multiply the 2016 QTY (625) by the Delta (11.25), it does not match the same result than doing at product level.

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

Hi Morgan,

I think you need to use INCLUDE.

You need to find ASP this and last year at product level prior to any aggregation.

eg this:

{ INCLUDE [Product]:([ASP 2016]-[ASP 2015])*sum([QTY 2016])}

You can then aggregate this result however you need to.

Regards,

Paul

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

Hi Paul,

However I need to divide the Delta Price by total 2016 turnover.

DP% = [Delta Price]/[2016 Turnover] doesn't work (aggregate level)

But reusing your methodology by setting [2016 Turnover] as

{ INCLUDE [Product]:([ASP 2016])*sum([QTY 2016])}

works at Product level, but not at activity level.

If I want to see the result at Activity, it will just do the sum of DP%.

Any suggestion?

Since this topic was started months ago and I did not succeed, I just created a new one here:

But problem stay the same

Thank you

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

DP% = sum([Delta Price])/sum([2016 Turnover])

1 of 1 people found this helpful