
1. Re: Calculation Order  Price Volume Mix Calculation
Adam Crahen Feb 28, 2016 8:00 PM (in response to Budi Lubis)Hi Budi
You need to aggregate the measures in your change formulas. Notice how mine say AGG instead of SUM.
Price Change should be:
(sum([2016  Price])  sum([2015  Price])) * sum([2015  Unit])
You had:
([2016  Price]  [2015  Price]) * [2015  Unit]
9.2 workbook attached.

PVM Sample.twbx 37.1 KB


2. Re: Calculation Order  Price Volume Mix Calculation
Budi Lubis Feb 29, 2016 7:57 AM (in response to Budi Lubis)Hi Adam and all,
Thank you so much for the quick reply. I knew there should be an easy solution to it
The only issue is on the aggregate level. I need to sum up the component instead of calculating it on the aggregate level. Any suggestion on how to force the aggregate to sum up from the components instead?

PVM Sample 2.twbx 61.6 KB


3. Re: Calculation Order  Price Volume Mix Calculation
pooja.gandhi Feb 29, 2016 8:00 AM (in response to Budi Lubis)2 of 2 people found this helpful 
4. Re: Calculation Order  Price Volume Mix Calculation
Budi Lubis Feb 29, 2016 8:32 AM (in response to pooja.gandhi)Dear Pooja and Adam,
Massive thank you for the quick reply.
If you would like to put the aggregate total in a graph, how would you enforce the compute using sum?
Thank you again in advanced for the help!!!!

PVM Sample 2.twbx 70.5 KB


5. Re: Calculation Order  Price Volume Mix Calculation
Adam Crahen Feb 29, 2016 8:42 AM (in response to Budi Lubis)The easy way is to add code to your column shelf. Turn on the Grand totals and hide the individual codes.
9.2 attached.

PVM Sample 2 (1).twbx 74.2 KB


6. Re: Calculation Order  Price Volume Mix Calculation
Adam Crahen Feb 29, 2016 8:49 AM (in response to Adam Crahen)
PVM Sample 2 (1).twbx 73.3 KB


7. Re: Calculation Order  Price Volume Mix Calculation
pooja.gandhi Feb 29, 2016 8:51 AM (in response to Budi Lubis)2 of 2 people found this helpfulYou can also create 3 additional calculated fields for each of the 3 measures and add those to the view, eliminates the need to hide individual measures each time:
But of course, you will have to be careful with filters while using LODs, because the order of execution works a certain way in Tableau.
More about it here: Filters and Level of Detail Expressions
The fact that I used INCLUDE would mean dimension filters are executed before the INCLUDE/EXCLUDE, so this should work in most cases.

8. Re: Calculation Order  Price Volume Mix Calculation
Budi Lubis Feb 29, 2016 9:29 AM (in response to pooja.gandhi)Adam and Pooja,
Again massive thank you for the help on the tricks.
Pooja, would it be possible to get the model with LOD calculation?
Thank you again for all the help!!!!

9. Re: Calculation Order  Price Volume Mix Calculation
Jacquelynn Henderson Dec 8, 2016 2:08 PM (in response to Adam Crahen)My price field is already a sum'd calc (sales/qty)... how do I sum as it's throwing off the overall calculation...

10. Re: Calculation Order  Price Volume Mix Calculation
Adam Crahen Dec 9, 2016 5:18 AM (in response to Jacquelynn Henderson)Hi Jacquelynn
You should start a new thread and post a sample workbook. Thanks.

11. Re: Calculation Order  Price Volume Mix Calculation
Jacquelynn Henderson Dec 9, 2016 6:19 AM (in response to Adam Crahen)I just need to sum the price field in this same calculation from the previous conversation that in my report is already an sum(sales)/sum(qty)= price. But I can't because of the error message. Is there another way to create the calculation around it?
***Price Change should be:
(sum([2016  Price])  sum([2015  Price])) * sum([2015  Unit])
Read more at https://community.tableau.com/thread/201792?start=0&tstart=0#QQgLIjZxbTLQLrmi.99

12. Re: Calculation Order  Price Volume Mix Calculation
Adam Crahen Dec 9, 2016 6:25 AM (in response to Jacquelynn Henderson)(SUM([YTD Qty])SUM([PYTD Qty))*[PYTD AUP]
The last calc is already and aggregate, that is what the error is telling you. It underlines the part in question with the red. Just remove the SUM wrapping [PYTD AUP].

14. Re: Calculation Order  Price Volume Mix Calculation
Adam Crahen Dec 9, 2016 6:46 AM (in response to Jacquelynn Henderson)Probably. That's why I asked for a workbook.
Can't help you with no info and partial screenshots.