5 Replies Latest reply on Mar 17, 2016 9:15 AM by andy.yu

# Table Calc Difference based on Dimension

Hello,

We're currently running Tableau Desktop 9.0.5

I was trying to play around with table calculations, but couldn't get it to work exactly what I needed it to do

• I was hoping to accomplish the following
• Find the difference of Actual and Budget 2 (5000.00 - 3000.00 = 2000.00)
• Find the difference of Actual and Budget 1 (5000.00 - 10000.00 = -5000.00)

• When I try to do table calculations, I know I can do relative to: Previous, Next, Last, First
• But it doesn't quite calculate correctly because I want to have the difference always based on "Actual"

Is this possible to do? Should I try to do a LOD expression if that helps?

Thanks for any advice or help!

• ###### 1. Re: Table Calc Difference based on Dimension

Tough to give a definitive answer without knowing the structure of the underlying data but you could use a FIXED LOD expression for the Actual, something along the lines of {Fixed [Category]: SUM([Actual])}. It all depends on the data structure whether this is appropriate for your specific case.

• ###### 2. Re: Table Calc Difference based on Dimension

The data kind of looks like the below:

typebudget_actualamount
CategoryBudget 15000
CategoryBudget 15000
CategoryBudget 21500
CategoryBudget 21500
CategoryActual2500
CategoryActual2500

So I'm not sure if maybe the data is not structured in a way to do what I need

• ###### 3. Re: Table Calc Difference based on Dimension

I think your data is at the wrong grain.

See this blog

I think you need to get Budgets into a column and Actuals into another column or another table.

• ###### 4. Re: Table Calc Difference based on Dimension

You could just create a simple calculated field: IF [Type] = 'Category' and [budget_actual] = 'Actual' THEN [Amount] END

You may need to wrap that in a MIN([AboveCalc]) if you have your data duplicated as your sample suggests

• ###### 5. Re: Table Calc Difference based on Dimension

Thanks everyone for the advice, I'll try to play around some more to see if it's possible or maybe do something with the data