Difference and percent difference in Tableau - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Difference Between Sales of Two Years

You have a sales dataset with yearly sales. You want to calculate the difference in sales between 2023 and 2022 using a calculated field in Tableau.

Which formula correctly calculates the difference?

A SUM([Sales]) - PREVIOUS_VALUE(SUM([Sales]))

B SUM([Sales]) - LOOKUP(SUM([Sales]), -1)

C SUM(IF [Year] = 2023 THEN [Sales] ELSE 0 END) - SUM(IF [Year] = 2022 THEN [Sales] ELSE 0 END)

D SUM([Sales]) - SUM(IF [Year] = 2021 THEN [Sales] ELSE 0 END)

💡 Hint

Think about filtering sales for each year explicitly before subtracting.

Option C explicitly sums sales for 2023 and subtracts sales for 2022, giving the correct difference.

Options B and C use table calculations that depend on data order and may not compare the correct years.

Option C subtracts 2021 sales, which is not the target year.

❓ visualization

intermediate

Best Visualization for Percent Difference Over Time

You want to show the percent difference in monthly sales compared to the previous month over one year.

Which visualization type best shows this percent difference clearly?

A Pie chart showing percent difference for each month

B Scatter plot with sales on x-axis and months on y-axis

C Bar chart with total sales per month

D Line chart with percent difference as the y-axis and months on the x-axis

💡 Hint

Think about how to show change over time clearly.

A line chart is best for showing trends and changes over time, making it ideal for percent difference month to month.

Pie charts do not show time progression well.

Bar charts show totals, not percent changes.

Scatter plots do not clearly show sequential time changes.

🧠 Conceptual

advanced

Understanding Percent Difference Calculation

Which formula correctly calculates the percent difference between current sales and previous sales?

A ((Current Sales - Previous Sales) / Previous Sales) * 100

B ((Previous Sales - Current Sales) / Current Sales) * 100

C ((Current Sales + Previous Sales) / Previous Sales) * 100

D (Current Sales / Previous Sales) * 100

💡 Hint

Percent difference shows how much current value changed compared to previous value.

Option A correctly calculates the percent change from previous to current.

Option A reverses the order and denominator, giving wrong sign and value.

Option A adds values, which is incorrect.

Option A calculates percent of previous, not difference.

🔧 Formula Fix

advanced

Identify the Error in Percent Difference Calculation

Given this Tableau calculated field for percent difference:

 (SUM([Sales]) - LOOKUP(SUM([Sales]), 1)) / LOOKUP(SUM([Sales]), 1) * 100 

What error will this cause?

A It causes division by zero error when next month sales are zero

B It compares current month sales to the next month instead of previous month

C It returns percent difference correctly with no error

D It causes syntax error due to missing parentheses

💡 Hint

Look at the offset number in the LOOKUP function.

LOOKUP with offset 1 looks forward one row, so it compares current sales to next month sales, not previous.

This reverses the intended comparison.

There is no syntax error and division by zero depends on data but is not guaranteed.

🎯 Scenario

expert

Create a Dashboard Showing Both Difference and Percent Difference

You have monthly sales data for two years. You want a dashboard that shows:

• Absolute difference in sales month over month
• Percent difference in sales month over month

Which combination of Tableau features will best achieve this?

A Use two calculated fields: one for difference using LOOKUP(SUM([Sales]), 0) - LOOKUP(SUM([Sales]), -1), and one for percent difference using ((LOOKUP(SUM([Sales]), 0) - LOOKUP(SUM([Sales]), -1)) / LOOKUP(SUM([Sales]), -1)) * 100; then show both in a dual-axis line chart with clear labels.

B Create one calculated field for difference and use quick table calculation for percent difference; display both as separate pie charts.

C Use only percent difference quick table calculation and show it in a bar chart; omit absolute difference to avoid clutter.

D Create calculated fields for difference and percent difference but show only difference in a scatter plot to highlight changes.

💡 Hint

Think about how to calculate both metrics and display them clearly together.

Option A correctly uses calculated fields with LOOKUP to get previous month sales, calculates difference and percent difference, and shows both metrics in a dual-axis line chart for easy comparison.

Option A uses pie charts which are not good for time series.

Option A omits absolute difference, losing important info.

Option A shows only difference and uses scatter plot which is less intuitive for time series.

Practice

1. What does the Difference calculation in Tableau typically show?

easy

A. The total sum of all values

B. The average value over time

C. How much a value changed between two points

D. The maximum value in a dataset

Solution

1. Step 1: Understand the meaning of Difference

   Difference measures the change in value from one point to another, showing increase or decrease.

2. Step 2: Compare with other options

   Sum, average, and maximum describe different calculations unrelated to change between points.

3. Final Answer:

   How much a value changed between two points -> Option C

4. Quick Check:

   Difference = Change amount [OK]

Hint: Difference = New value minus old value [OK]

Common Mistakes:

• Confusing difference with total sum
• Thinking difference shows average
• Mixing difference with maximum value

2. Which of the following Tableau calculations correctly computes the percent difference between the current and previous value?

easy

A. ([Current Value] - [Previous Value]) / [Previous Value]

B. ([Previous Value] - [Current Value]) / [Current Value]

C. [Current Value] + [Previous Value] / [Previous Value]

D. [Current Value] * [Previous Value]

Solution

1. Step 1: Recall percent difference formula

   Percent difference = (New - Old) / Old, showing change relative to original.

2. Step 2: Match formula to options

   ([Current Value] - [Previous Value]) / [Previous Value] matches formula exactly; others either reverse or misuse operations.

3. Final Answer:

   ([Current Value] - [Previous Value]) / [Previous Value] -> Option A

4. Quick Check:

   Percent difference = (New - Old) / Old [OK]

Hint: Percent difference = difference divided by previous value [OK]

Common Mistakes:

• Swapping numerator terms
• Dividing by current value instead of previous
• Using addition or multiplication instead of division

3. Given this Tableau calculated field:
LOOKUP(SUM([Sales]), 0) - LOOKUP(SUM([Sales]), -1)
What does this calculation return?

medium

A. The percent difference in sales between current and previous row

B. The total sales for all rows

C. The sum of sales for the previous row only

D. The difference in sales between the current and previous row

Solution

1. Step 1: Understand LOOKUP function usage

   LOOKUP(SUM([Sales]), 0) returns current row sales; LOOKUP(SUM([Sales]), -1) returns previous row sales.

2. Step 2: Calculate difference

   Subtracting previous from current gives the difference in sales between rows.

3. Final Answer:

   The difference in sales between the current and previous row -> Option D

4. Quick Check:

   LOOKUP difference = sales change [OK]

Hint: LOOKUP with offset -1 gets previous row value [OK]

Common Mistakes:

• Thinking it calculates percent difference
• Assuming it sums all sales
• Confusing current and previous row values

4. You wrote this Tableau formula to calculate percent difference:
(SUM([Sales]) - LOOKUP(SUM([Sales]), -1)) / SUM([Sales])
But the results seem incorrect. What is the likely error?

medium

A. Not using WINDOW_SUM instead of SUM

B. Dividing by current sales instead of previous sales

C. Subtracting current sales from previous sales

D. Using LOOKUP with offset -1 incorrectly

Solution

1. Step 1: Analyze denominator in formula

   The formula divides by SUM([Sales]) which is current sales, but percent difference should divide by previous sales.

2. Step 2: Identify correct denominator

   Correct formula divides by LOOKUP(SUM([Sales]), -1) to get previous sales as denominator.

3. Final Answer:

   Dividing by current sales instead of previous sales -> Option B

4. Quick Check:

   Percent difference denominator = previous value [OK]

Hint: Divide by previous value, not current, for percent difference [OK]

Common Mistakes:

• Using current value as denominator
• Confusing subtraction order
• Misusing LOOKUP offset

5. You want to create a Tableau dashboard showing monthly sales percent difference compared to the previous month. Which approach correctly calculates this and handles months with zero sales without errors?

hard

A. Use (SUM([Sales]) - LOOKUP(SUM([Sales]), -1)) / NULLIF(LOOKUP(SUM([Sales]), -1), 0) to avoid division by zero

B. Use (SUM([Sales]) - LOOKUP(SUM([Sales]), -1)) / SUM([Sales]) ignoring zero sales

C. Use SUM([Sales]) / LOOKUP(SUM([Sales]), -1) without subtraction

D. Use WINDOW_SUM(SUM([Sales])) - WINDOW_SUM(SUM([Sales])) for difference

Solution

1. Step 1: Calculate percent difference with zero check

   Percent difference = (Current - Previous) / Previous; use NULLIF to avoid division by zero errors when previous sales are zero.

2. Step 2: Evaluate options for correctness

   Use (SUM([Sales]) - LOOKUP(SUM([Sales]), -1)) / NULLIF(LOOKUP(SUM([Sales]), -1), 0) to avoid division by zero uses NULLIF to prevent errors; others either divide by current or ignore zero sales causing errors or wrong results.

3. Final Answer:

   Use (SUM([Sales]) - LOOKUP(SUM([Sales]), -1)) / NULLIF(LOOKUP(SUM([Sales]), -1), 0) to avoid division by zero -> Option A

4. Quick Check:

   Use NULLIF to handle zero denominator [OK]

Hint: Use NULLIF to prevent division by zero in percent difference [OK]

Common Mistakes:

• Dividing by current sales instead of previous
• Ignoring zero sales causing errors
• Using sum differences without percent calculation