Creating calculated fields in Tableau - Practice Exercises

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Total Sales per Region using LOD Expression

You have a sales dataset with fields: [Sales], [Region], and [Date]. You want to create a calculated field that shows the total sales for each region regardless of the date filter applied. Which Tableau LOD expression will correctly calculate this?

A {FIXED [Region] : SUM([Sales])}

B SUM([Sales])

C {EXCLUDE [Region] : SUM([Sales])}

D {INCLUDE [Region] : SUM([Sales])}

💡 Hint

Think about how to fix the calculation at the region level ignoring other filters.

The FIXED LOD expression calculates the sum of sales fixed at the region level, ignoring other dimensions like date filters. INCLUDE and EXCLUDE adjust the level of detail differently, and SUM([Sales]) respects all filters.

❓ visualization

intermediate

Identify the Correct Visualization for a Calculated Profit Ratio

You created a calculated field in Tableau called Profit Ratio defined as SUM([Profit]) / SUM([Sales]). Which visualization best shows the profit ratio by product category with clear comparison?

A Pie chart showing total sales by product category

B Line chart showing profit ratio trend over time

C Stacked bar chart showing profit and sales side by side

D Bar chart with product categories on the x-axis and profit ratio on the y-axis

💡 Hint

You want to compare profit ratios across categories clearly.

A bar chart with product categories on the x-axis and profit ratio on the y-axis clearly compares the ratio across categories. Stacked bars and pie charts do not show ratios well, and line charts are better for trends over time.

❓ data_modeling

advanced

Create a Calculated Field to Show Year-over-Year Sales Growth

You have a dataset with [Sales] and [Order Date]. You want to create a calculated field in Tableau that shows the percentage growth of sales compared to the previous year. Which formula correctly calculates this?

A (SUM([Sales]) - LOOKUP(SUM([Sales]), -1)) / LOOKUP(SUM([Sales]), -1)

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

C (SUM([Sales]) - LOOKUP(SUM([Sales]), 1)) / LOOKUP(SUM([Sales]), 1)

D (SUM([Sales]) - PREVIOUS_VALUE(SUM([Sales]))) / PREVIOUS_VALUE(SUM([Sales]))

💡 Hint

Consider handling null values for the first year.

Option B uses ZN() to replace nulls with zero, preventing errors for the first year. It calculates the difference between current and previous year sales divided by previous year sales, which is the correct formula for year-over-year growth.

🔧 Formula Fix

advanced

Identify the Error in This Calculated Field for Average Sales

You wrote this calculated field in Tableau to find average sales per customer: SUM([Sales]) / COUNTD([Customer ID]). However, the result is incorrect when filters are applied. What is the main reason?

Tableau

SUM([Sales]) / COUNTD([Customer ID])

A The calculation does not use FIXED LOD, so filters affect numerator and denominator differently.

B COUNTD cannot be used in calculated fields.

C SUM([Sales]) should be replaced with AVG([Sales]) for average calculation.

D The formula needs to use WINDOW_SUM instead of SUM.

💡 Hint

Think about how filters affect aggregation in numerator and denominator.

Filters can reduce the number of customers counted but also reduce sales sum differently, causing incorrect averages. Using FIXED LOD expressions can fix this by fixing the denominator or numerator at the desired level.

🎯 Scenario

expert

Design a Calculated Field to Flag Top 10% Customers by Sales

You want to create a calculated field in Tableau that flags customers whose total sales are in the top 10% of all customers. Which formula correctly creates this flag?

A IF PERCENTILE(SUM([Sales]), 0.9) THEN 'Top 10%' ELSE 'Others' END

B IF RANK_PERCENTILE(SUM([Sales])) <= 0.1 THEN 'Top 10%' ELSE 'Others' END

C IF RANK_PERCENTILE(SUM([Sales])) >= 0.9 THEN 'Top 10%' ELSE 'Others' END

D IF WINDOW_PERCENTILE(SUM([Sales]), 0.9) THEN 'Top 10%' ELSE 'Others' END

💡 Hint

Remember that top 10% means percentile rank greater than or equal to 0.9.

Option C uses RANK_PERCENTILE to rank customers by sales and flags those with percentile rank 0.9 or higher as top 10%. Option C reverses the logic, and options A and D use invalid functions or syntax.