Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 35 ยท Practice

Tableau Calculation Practice with Solutions

Ten Tableau calculation exercises with answers: IF and CASE, ratios, LOD expressions, running totals, ranks, percent of total and dates, verified in SQL and pandas.

Reading about calculated fields only takes you so far. This lesson is a workout: ten exercises, from simple conditional logic to level of detail expressions and table calculations. Try each one yourself in Tableau before opening the solution. Every solution is a Tableau formula, which cannot run in the browser and is shown as read-only code, followed by a stand-in (SQL or pandas) that computes the expected answer from the same data, so you can check your own Tableau result against it.

All exercises use the same small table of orders: Order ID, Customer, Region, Category, Order Date, Sales and Profit.

Order ID Customer Region Category Order Date Sales Profit
1 Ana East Chairs 2025-01-10 200 40
2 Ana East Desks 2025-01-25 500 50
3 Ben West Chairs 2025-02-03 150 -10
4 Ben West Lamps 2025-02-18 60 24
5 Cy East Desks 2025-03-07 800 160
6 Dee West Chairs 2025-03-21 300 45
7 Dee North Desks 2025-04-09 450 27
8 Ana East Lamps 2025-04-30 90 36

Ten exercises

Logic IF, CASE, ISNULL.
Ratios Margin, share, average.
LOD FIXED, INCLUDE, EXCLUDE.
Table calcs Running sum, rank, % of total.

Try first, then check.

1 / 2

Part A: logic and ratios

Exercise 1. Label each order High if sales are 400 or more, Medium if 150 to 399, otherwise Low.

Exercise 2. Compute the profit margin per category (sum of profit over sum of sales), not an average of per-order margins.

Exercise 3. Flag orders that lost money, and show the share of orders that lost money.

Solutions in Tableau:

Text
// 1. Order Size
IF [Sales] >= 400 THEN "High"
ELSEIF [Sales] >= 150 THEN "Medium"
ELSE "Low" END

// 2. Margin (use in a view with Category on Rows)
SUM([Profit]) / SUM([Sales])

// 3a. Loss flag (row level)
[Profit] < 0
// 3b. Share of loss orders (aggregate)
SUM(IF [Profit] < 0 THEN 1 ELSE 0 END) / COUNT([Order ID])
SQL Check exercises 1 to 3

Press Run (or Ctrl + Enter) to see the result here.

For Chairs the true margin is 75/650, about 11.5%, but the average of the three per-order margins (20%, -6.7%, 15%) is 9.4%, a different number. That is the "average of averages" trap from Common Tableau Mistakes.

Part B: level of detail

Exercise 4. Show each customer's total sales next to every order row (so you can compute each order's share of its customer's total).

Exercise 5. For each order, the customer's first order date, and the number of days since it (a "customer age" field).

Exercise 6. The average sales per customer, across customers: first total per customer, then the average of those totals.

Text
// 4. Customer Total
{ FIXED [Customer] : SUM([Sales]) }
// share of customer total
SUM([Sales]) / MIN([Customer Total])      // MIN of a constant per customer

// 5. First Order and age
{ FIXED [Customer] : MIN([Order Date]) }
DATEDIFF('day', [First Order], [Order Date])

// 6. Average per customer
AVG({ FIXED [Customer] : SUM([Sales]) })
Python Check exercises 4 to 6

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Exercise 6 shows why the LOD matters: the average per customer (637.5) differs from the average per order (318.75), because the first groups before it averages. Compare it with the order of operations: FIXED runs before dimension filters, so the customer total ignores a filter on Category, which may or may not be what you want.

Part C: table calculations

Exercise 7. A monthly running total of sales.

Exercise 8. Rank the categories by sales (1 = highest).

Exercise 9. Each region's percent of total sales.

Exercise 10. A moving average of monthly sales over the current and previous month.

Text
// 7. Running Sum
RUNNING_SUM(SUM([Sales]))

// 8. Rank
RANK(SUM([Sales]))              // use RANK_DENSE for no gaps

// 9. % of total (or the quick table calc "Percent of Total")
SUM([Sales]) / TOTAL(SUM([Sales]))

// 10. Moving average (current and previous month)
WINDOW_AVG(SUM([Sales]), -1, 0)

In every case, set Compute Using (the "addressing"): for a monthly running total it is Table (Across) with month on Columns; the partitioning fields (those that restart the calculation) are the other dimensions in the view.

Python Check exercises 7 to 10

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

rank(method="min") matches Tableau's default RANK, which gives tied values the same rank and skips the next one; use method="dense" for RANK_DENSE.

Stretch exercises

If these were easy, try them without a solution (verify with SQL or pandas):

  1. Year-to-date sales compared with the same period last year, as a percentage.
  2. Mark each customer as New (first order in the last 90 days) or Returning.
  3. A calculation that returns the second-highest region by sales.
  4. A running total that restarts every quarter.
  5. Sales for the Chairs category as a share of all categories, ignoring a Category filter (hint: a FIXED expression).
  6. A calculation that returns the share of orders with profit above the median profit.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which formula computes each customer's total sales on every order row, regardless of Category filters?

    A FIXED LOD expression at customer level, computed before ordinary dimension filters.
  2. 2. What is the difference between RANK and RANK_DENSE for ties?

    With two values tied for first, RANK gives 1, 1, 3 while RANK_DENSE gives 1, 1, 2.
  3. 3. In a monthly running total, what does 'Compute Using' control?

    Addressing fields define the direction; partitioning fields define the groups in which it restarts.
  4. 4. Why is SUM(Profit)/SUM(Sales) preferred over AVG of per-order margins?

    A ratio of sums gives the right weighted margin. An average of ratios treats big and small orders equally.

Summary

  • Logic: IF/ELSEIF/CASE; ratios: sum, then divide.
  • LOD: FIXED for stable per-customer or per-cohort values; they ignore dimension filters.
  • Table calculations: RUNNING_SUM, RANK, TOTAL, WINDOW_AVG; set addressing and partitioning with Compute Using.
  • Check every formula against an independent SQL or pandas calculation.

Next: Tableau Interview Questions