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.