Difference and percent difference in Tableau - Cell-by-Cell Formula Trace

Sample Data

Monthly sales data for four months

Cell	Value
A1	Month
B1	Sales
A2	Jan
B2	1000
A3	Feb
B3	1200
A4	Mar
B4	900
A5	Apr
B5	1500

Formula Trace

Difference = SUM([Sales]) - LOOKUP(SUM([Sales]), -1) Percent Difference = (Difference) / LOOKUP(SUM([Sales]), -1)

Step 1: SUM([Sales]) at Feb = 1200

Step 2: LOOKUP(SUM([Sales]), -1) at Feb = SUM([Sales]) at Jan = 1000

Step 3: Difference at Feb = 1200 - 1000

Step 4: Percent Difference at Feb = 200 / 1000

Step 5: SUM([Sales]) at Mar = 900

Step 6: LOOKUP(SUM([Sales]), -1) at Mar = SUM([Sales]) at Feb = 1200

Step 7: Difference at Mar = 900 - 1200

Step 8: Percent Difference at Mar = -300 / 1200

Step 9: SUM([Sales]) at Apr = 1500

Step 10: LOOKUP(SUM([Sales]), -1) at Apr = SUM([Sales]) at Mar = 900

Step 11: Difference at Apr = 1500 - 900

Step 12: Percent Difference at Apr = 600 / 900

Cell Reference Map

     A       B
  +-------+-------+
1 | Month | Sales |
  +-------+-------+
2 | Jan   | 1000  |
  +-------+-------+
3 | Feb   | 1200  | <-- Uses B3 and LOOKUP to B2
  +-------+-------+
4 | Mar   | 900   | <-- Uses B4 and LOOKUP to B3
  +-------+-------+
5 | Apr   | 1500  | <-- Uses B5 and LOOKUP to B4
  +-------+-------+

The formula uses current month sales (column B) and previous month sales via LOOKUP function to calculate difference and percent difference.

Result

     A       B       C           D
  +-------+-------+-----------+--------------+
1 | Month | Sales | Difference| Percent Diff |
  +-------+-------+-----------+--------------+
2 | Jan   | 1000  |           |              |
  +-------+-------+-----------+--------------+
3 | Feb   | 1200  | 200       | 0.20         |
  +-------+-------+-----------+--------------+
4 | Mar   | 900   | -300      | -0.25        |
  +-------+-------+-----------+--------------+
5 | Apr   | 1500  | 600       | 0.6667       |
  +-------+-------+-----------+--------------+

The Difference column shows the change in sales from the previous month. The Percent Diff column shows that change as a decimal fraction of the previous month's sales.

Sheet Trace Quiz - 3 Questions

What is the Difference value for March?

A 300

B -300

C 2100

D 900

Key Result

Difference = Current value minus previous value; Percent Difference = Difference divided by previous value