4 Replies Latest reply on Nov 20, 2018 9:06 AM by Jeremy zhang

Calculate on the result of in table calculation

Hi Gurus

There are four columns in this table and column 3 and 4 are in table calumniation. I need to have a column 5 ,to calculate the % difference of column 3 and 4. Please help!    My tableau version is 8.2

Thank you very much in advance!

Jeremy

• 1. Re: Calculate on the result of in table calculation

Wow Tableau 8.2! I'm afraid I don't still have this version installed to post and example, but I can talk you through it.

When you use the "quick table calculation" to do share of total, Tableau (in the background) is creating a formula which looks like this

SUM([Sales 2017]) / TOTAL(SUM([Sales 2017]))  for Sales 2017

and

SUM([Sales 2018]) / TOTAL(SUM([Sales 2018]))  for Sales 2018

If you create these as 2 formulas (calculated fields) you can then create a further calculation taking one away from the other to get the variance.

Hope that makes sense, and does the trick

• 2. Re: Calculate on the result of in table calculation

Thank you very much Simon!!

I created the 2 formulas, but it does not work as expected.

So I created 4 calculations to see where is the issue.

Calculation 1= SUM([\$ Sales 2018])

Calculation 2= total(SUM([\$ Sales 2018]))

Calculation 3= [Calculation1]/[Calculation2]

Calculation 4= SUM([\$ Sales 2018]) / TOTAL(SUM([\$ Sales 2018]))  I do not want calculation 4 do the in table calculation, but could not find a way

Which part I did incorrectly? Calculation 3 and 4 should not show 0

I hope I can attached my tableau file, but it is too big to be attached here.

Thank you again!

Jeremy

• 3. Re: Calculate on the result of in table calculation

So I think your calculations are (in the main) correct, it's just that you haven't set the display to percentage. As you have them as regular numbers to zero decimal places Tableau is rounding them to zero (eg the first product is 42.88% = 0.4488 which rounds to zero). If you change them to percent it should show the right results.

I say in the main, as generally when you are comparing 2 percentages you want the difference, so want to take one away from the other and not divide them. I don't know your exact use-case, and there are times you would want to do this (although again, generally, you'd show the difference not the ratio), but again change the formatting of this field to a percentage and it should show.

• 4. Re: Calculate on the result of in table calculation

You are right!! I think I got what I need now.

Thank you very much!!