1 2 Previous Next 17 Replies Latest reply on May 3, 2017 9:36 AM by Alex Martino

# Calculation Order - Price Volume Mix Calculation

I am trying to calculate a YOY price volume mix but can't seem to figure out to why Tableau can't seem to give the right answer; the following is the simple data set for the example.

Code
DatePriceUnit
Sales
A1/1/2015\$34\$12
A1/1/2016\$46\$24

Here are the formulas and I created calculated fields to pull each of the variables:

• Price Change = (2016 Price - 2015 Price) * 2015 Unit = (\$4 - \$3) * 4 =  \$4
• Volume Change = (2016 Unit - 2015 Unit) * 2015 Price = (6-4) * \$3 = \$6
• Mix Change = (2016 Price - 2015 Price) * (2016 Unit - 2015 Unit) = \$2

For the price change and volume change calculation, somehow Tableau always return 0 for 2016 unit and 2016 price that resulted in -\$12, results below.  As for the mix change, I have no idea to why Tableau is returning \$36.  Any clue?

Thank you in advanced for the help!

• ###### 1. Re: Calculation Order - Price Volume Mix Calculation

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])

([2016 - Price] - [2015 - Price]) * [2015 - Unit]

9.2 workbook attached.

2 of 2 people found this helpful
• ###### 2. Re: Calculation Order - Price Volume Mix Calculation

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?

1 of 1 people found this helpful
• ###### 3. Re: Calculation Order - Price Volume Mix Calculation

For the last 3 cells, change compute using sum by clicking on the cell:

2 of 2 people found this helpful
• ###### 4. Re: Calculation Order - Price Volume Mix Calculation

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!!!!

1 of 1 people found this helpful
• ###### 5. Re: Calculation Order - Price Volume Mix Calculation

The easy way is to add code to your column shelf. Turn on the Grand totals and hide the individual codes.

9.2 attached.

2 of 2 people found this helpful
• ###### 6. Re: Calculation Order - Price Volume Mix Calculation

Here is how you force the sum on the graph.

1 of 1 people found this helpful
• ###### 7. Re: Calculation Order - Price Volume Mix Calculation

You 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.

2 of 2 people found this helpful
• ###### 8. Re: Calculation Order - Price Volume Mix Calculation

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

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

Hi Jacquelynn-

You should start a new thread and post a sample workbook.  Thanks.

• ###### 11. Re: Calculation Order - Price Volume Mix Calculation

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])

• ###### 12. Re: Calculation Order - Price Volume Mix Calculation

(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].

• ###### 13. Re: Calculation Order - Price Volume Mix Calculation

But then I get the wrong value in the calculation... I entered a dummy line of just i.e.\$139 which is what the AUP is and I get the right number on the test one.  Can I use an LOD to fix the Price value?

• ###### 14. Re: Calculation Order - Price Volume Mix Calculation

Probably.    That's why I asked for a workbook.