5 Replies Latest reply on Mar 15, 2016 3:10 AM by Steve Martin

# How to calculate the following...

There is a table below --

ScenarioName1    ScenarioName2     ScenarioName3     ScenarioName4   ScenarioName5

SaleAmt1        \$2M                      \$7M

SaleAmt2        \$3M                      \$9M

SaleAmt3        \$4M                      ...

ScenarioTotal   \$9M

ScenarioName is a COLUMN name w/ values ScenarioName1, 2, 3, 4, 5, and the SaleAmt1, SaleAmt2 & SaleAmt3 are also the COLUMN names directly from SQL Server DB and their values are pulled out as the measured values in ROW on the Tableau side.

In order to calculate (the cell ScenarioName2/SaleAmt1's value - the cell ScenarioName1 / SaleAmt1's value) / Total for Baseline ScenarioName1's Value, namely, (\$7M - \$2M)/\$9M.

Then same for (the cell ScenarioName2/SaleAmt2's value - the cell ScenarioName1 / SaleAmt2's value) / Total for Baseline ScenarioName1's Value, namely, (\$7M - \$3M)/\$9M...similarly to continue...

Then insert the "new calculated fields"  (showing %)  b/w ScenarioName2 & ScenarioName3, ScenarioName3 & ScenarioName4... Is there a one-click feature in Tableau to get it? How to do it?

• ###### 1. Re: How to calculate the following...

Hi Elle,

Please can you clarify the calculations you need to produce your numbers eg ScenarioName3 = ScenarioName2 / ScenarioTotal

Better yet, please can you provide an Excel workbook detailing how your raw data will get to Tableau and the calcs you need so we can see how you got to your output.

To answer your question, yes, the calculation you require is simple you simply identify the position of your value using a Lookup(,0) calc and then perform you calc against the Total so assuming the calculation is ScenarioName3 = ScenarioName2 / ScenarioName1Total you calc would be:

Lookup(Sum(ScenarioName2),0) / Total(Sum(ScenarioName1))

Steve

• ###### 2. Re: How to calculate the following...

Thx, Steve!! For some reason, I can't make it work using Lookup() -- either to create the right calculations as illustrated in my original message, or be able to insert the new calculated fields b/w ScenarioName2&3, ScenarioName3&4... Actually I am pulling date directly from SQL Server. Column ScenarioName in DB just has values like ScenarioName1, ScenarioName2, 3, 4, 5, while SaleAmt1, SaleAmt2... columns in DB have the actual values, and they are used as Measured Values and put onto the Row in Tableau view. Could u pls advise what can be done? Thx a bunch!!

• ###### 3. Re: How to calculate the following...

Unless you have a specific purpose, you will not need the lookup function, your calc is just the sum over the total so in this instance building on from my previous post assuming ScenarioName3 = ScenarioName2 / ScenarionName1Total then your calc would be:

Sum(ScenarioName2) / Total(Sum(ScenarioName1))

If you are struggling with this, please can you provide an excel worksheet with your calcs and a packaged workbook ensuring your uploaded data is sanitised or using Superstore Sales.

Steve

1 of 1 people found this helpful
• ###### 4. Re: How to calculate the following...

thx, steve! i think now i got it -- there is NO way to insert the new tableau calculated fields b/w scenarioName1, scenarioName2, 3, 4, 5, as they are NOT numeric columns, one can only insert the new Tableau calculated fields b/w the numeric columns SaleAmt1, SaleAmt2, 3. pls correct me if my understanding is wrong.

• ###### 5. Re: How to calculate the following...

I am uncertain as to why you need to insert the calculation between text fields - this is different from your original post and table layout above.

Please can you fully explain your requirement and post a workbook so that I can see exactly what it is you are trying to do; you can use Superstore Sales as your data.

Steve