Tableau Calculation Types

Prasanna Parasurama

Types of calculation

  1. Row-level calculations
    • Calculations are the level of observation of the dataset
  2. Aggregate-level calculations
    • Calculations are at the level of aggregation/granularity on the viz
  3. Table Calculations
    • Additional calculations using values from the viz
  4. Level of Detail Calculation
    • Increase (INCLUDE), decrease (EXCLUDE) or fix (FIX) level of aggregation/granularity on the viz

What is this business’s profit margin?

Order Sales Profit Order margin
A $900 $90 10%
B $100 −$50 −50%

Write down your calculation, then compare your answer with a partner.

Two ways to calculate profit margin

Order Sales Profit Order margin
A $900 $90 10%
B $100 −$50 −50%

Overall business margin

90−50900+100=𝟒% \frac{90-50}{900+100}=\mathbf{4\%} 

SUM([Profit]) / SUM([Sales])

Mean order-level margin

10%+(−50%)2=−𝟐𝟎% \frac{10\%+(-50\%)}{2}=\mathbf{-20\%} 

AVG([Profit] / [Sales])

Which business question does each calculation answer?

Row vs. Agg Calculation

ProfitMarginAgg

SUM([Profit])/SUM([Sales])

Overall profit margin in each subcategory

ProfitMarginRow

[Profit]/[Sales]

Avg Profit margin PER order in each sub-category

Sub-Category ProfitMarginAgg Avg. ProfitMarginRow
Accessories 0.2505 0.2182
Appliances 0.1694 -0.1490
Art 0.2405 0.2522
Binders 0.1516 -0.1946
Bookcases -0.0315 -0.1351
Chairs 0.0811 0.0471
Copiers 0.3721 0.3204
Envelopes 0.4228 0.4235
Fasteners 0.2846 0.3063
Furnishings 0.1453 0.1398
Labels 0.4390 0.4246
Machines 0.0182 -0.0689
Paper 0.4339 0.4250
Phones 0.1358 0.1225
Storage 0.0948 0.0908
Supplies -0.0251 0.1144
Tables -0.0853 -0.1402

Sample Superstore Dataset

  • Each order denoted by oo
    • non_o total orders
  • Each sub-category denoted by kk
    • nkn_k total sub categories
  • Each manufacturer denoted by mm
    • nmn_m total manufacturers
  • Let Po,k,mP_{o,k,m} be the profit ($) of order oo in category kk manufactured by mm
  • Let So,k,mS_{o,k,m} be the sales ($) of order oo in category kk manufactured by mm
  • We want to calculate Profit Margin (profit/sales) at different levels

Profit Margin at different levels

Avg profit margin per order

PM=1no∑oPoSo PM = \frac{1}{n_o}\sum_o\frac{P_o}{S_o}

Row-level calculation and then aggregation

Profit margin per subcategory

PM(k)=∑oPo,k∑oSo,k PM(k) = \frac{\sum_o P_{o,k}}{\sum_o S_{o,k}}

Aggregate-level calculation

PM at the sub-category, manufacturer level

  • I want to calculate profit margin at the (sub-category, manufacturer) level, aggregated up to a subcategory
    • i.e., Avg. profit margin per manufacturer for a given sub-category
    • Need to use LOD calculation. Why?

PM(k,m)=∑oPo,k,m∑oSo,k,m PM(k,m) = \frac{\sum_o P_{o,k,m}}{\sum_o S_{o,k,m}}

PM(k)=1nm∑mPM(k,m)=1nm∑m∑oPo,k,m∑oSo,k,m PM(k) = \frac{1}{n_m}\sum_m PM(k,m) = \frac{1}{n_m}\sum_m\frac{\sum_o P_{o,k,m}}{\sum_o S_{o,k,m}}

With [Sub-Category] in the view:

Average manufacturer profit margin

AVG(
  { INCLUDE [Manufacturer] :
      SUM([Profit]) / SUM([Sales])
  }
)

INCLUDE calculates a margin for each manufacturer; AVG averages those margins.

LOD Syntax

  • FIXED [Dim1] [Dim2]
    • Similar to GROUP_BY({Dim1, Dim2})
  • INCLUDE [Dim 2]
    • Similar to GROUP_BY({LOD of Viz} + {Dim2})
  • EXCLUDE [Dim 2]
    • Similar to GROUP_BY({LOD of Viz} - {Dim2})

Tableau Table Calculations

  • Tableau table calculations are additional calculations using values on the viz
    • Running total
    • Difference from previous
    • Etc.

If you need a refresher on summation notation