Arithmetic calculations in Tableau - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Total Sales with Discount Applied

You have a sales dataset with fields Sales Amount and Discount Rate (as a decimal). Which Tableau calculated field formula correctly calculates the total sales after applying the discount?

A [Sales Amount] + [Discount Rate]

B [Sales Amount] / [Discount Rate]

C [Sales Amount] * (1 - [Discount Rate])

D [Sales Amount] - [Discount Rate]

💡 Hint

Remember that a discount reduces the sales amount by a percentage.

The correct formula multiplies the sales amount by (1 minus the discount rate) to reduce the sales by the discount percentage.

❓ visualization

intermediate

Best Visualization for Showing Profit Margin Percentage

You want to show the profit margin percentage for different product categories. Which visualization type in Tableau best communicates this percentage clearly?

A Bar chart with profit margin percentage on the axis

B Line chart showing profit margin over time

C Scatter plot with profit margin on one axis and sales on the other

D Pie chart showing profit margin slices per category

💡 Hint

Think about which chart type makes comparing percentages across categories easiest.

A bar chart with profit margin percentage on the axis allows easy comparison of margin percentages across categories.

❓ data_modeling

advanced

Create a Calculated Field for Average Sales per Customer

You have total sales and number of customers per region. Which Tableau calculated field formula correctly computes average sales per customer?

A [Number of Customers] / [Total Sales]

B [Total Sales] / [Number of Customers]

C [Total Sales] * [Number of Customers]

D [Total Sales] - [Number of Customers]

💡 Hint

Average means total divided by count.

Average sales per customer is total sales divided by number of customers.

🎯 Scenario

advanced

Adjusting Sales for Tax in a Dashboard

Your sales data does not include tax. You want to show sales including a 7% tax in your dashboard. Which calculated field formula correctly adds tax to sales?

A [Sales] / 1.07

B [Sales] + 0.07

C [Sales] - 0.07

D [Sales] * 1.07

💡 Hint

Adding 7% tax means increasing sales by 7%.

Multiplying sales by 1.07 increases sales by 7%, correctly adding tax.

🔧 Formula Fix

expert

Identify the Error in This Tableau Calculation

Consider this Tableau calculated field formula intended to calculate profit ratio:
[Profit] / [Sales Amount]
However, the dashboard shows Null values for some rows. What is the most likely cause?

Tableau

[Profit] / [Sales Amount]

A Some [Sales Amount] values are zero, causing division by zero resulting in Null

B [Profit] field contains text values causing calculation failure

C [Sales Amount] field is missing from the data source

D The formula syntax is incorrect and causes an error

💡 Hint

Think about what happens when you divide by zero in Tableau.

Dividing by zero in Tableau results in Null values, which explains the Nulls in the dashboard.

Practice

1. In Tableau, which operator is used to multiply two fields [Sales] and [Quantity] to calculate total revenue?

easy

A. Use the * operator like [Sales] * [Quantity]

B. Use the + operator like [Sales] + [Quantity]

C. Use the / operator like [Sales] / [Quantity]

D. Use the - operator like [Sales] - [Quantity]

Solution

1. Step 1: Identify the arithmetic operation for total revenue

   Total revenue is calculated by multiplying sales price by quantity sold.

2. Step 2: Use the multiplication operator in Tableau

   Tableau uses the * symbol to multiply fields, so [Sales] * [Quantity] is correct.

3. Final Answer:

   Use the * operator like [Sales] * [Quantity] -> Option A

4. Quick Check:

   Multiplication = * [OK]

Hint: Multiply fields with * operator inside square brackets [OK]

Common Mistakes:

• Using + instead of * for multiplication
• Forgetting to put field names in square brackets
• Using / or - operators incorrectly

2. Which of the following is the correct syntax to calculate the average sales per order in Tableau?

easy

A. SUM([Sales]) / COUNT([Order ID])

B. SUM([Sales]) * COUNT([Order ID])

C. AVG([Sales]) + COUNT([Order ID])

D. SUM([Sales]) - COUNT([Order ID])

Solution

1. Step 1: Understand average sales per order calculation

   Average sales per order equals total sales divided by number of orders.

2. Step 2: Use correct aggregation functions and operators

   Use SUM([Sales]) to get total sales and COUNT([Order ID]) to count orders, then divide.

3. Final Answer:

   SUM([Sales]) / COUNT([Order ID]) -> Option A

4. Quick Check:

   Average = SUM / COUNT [OK]

Hint: Divide total sales by order count using SUM and COUNT [OK]

Common Mistakes:

• Multiplying instead of dividing sales and order count
• Using AVG([Sales]) without counting orders
• Adding or subtracting instead of dividing

3. Given the following Tableau calculation:
SUM([Profit]) / SUM([Sales])
What does this calculation represent?

medium

A. The total profit plus total sales

B. The difference between profit and sales

C. The profit margin as a ratio of profit to sales

D. The average sales per profit

Solution

1. Step 1: Analyze the calculation components

   The formula divides total profit by total sales using SUM aggregation.

2. Step 2: Interpret the meaning of the ratio

   Profit divided by sales gives the profit margin ratio, showing profitability percentage.

3. Final Answer:

   The profit margin as a ratio of profit to sales -> Option C

4. Quick Check:

   Profit margin = SUM(Profit) / SUM(Sales) [OK]

Hint: Divide total profit by total sales to get profit margin [OK]

Common Mistakes:

• Thinking it adds or subtracts profit and sales
• Confusing ratio with average
• Ignoring aggregation functions

4. You wrote this Tableau calculation:
SUM([Sales] + [Profit])
But it gives an error. What is the correct way to fix it?

medium

A. Change to SUM([Sales]) * SUM([Profit])

B. Change to SUM([Sales]) + SUM([Profit])

C. Remove SUM and write [Sales] + [Profit]

D. Use AVG([Sales] + [Profit]) instead

Solution

1. Step 1: Understand aggregation rules in Tableau

   You cannot add fields inside SUM directly; each field must be aggregated separately.

2. Step 2: Correct the syntax by summing each field then adding

   Use SUM([Sales]) + SUM([Profit]) to sum each field first, then add results.

3. Final Answer:

   Change to SUM([Sales]) + SUM([Profit]) -> Option B

4. Quick Check:

   Aggregate separately, then add [OK]

Hint: Sum fields separately before adding [OK]

Common Mistakes:

• Adding fields inside SUM causing syntax error
• Multiplying sums instead of adding
• Removing aggregation causing errors

5. You want to create a calculated field in Tableau that shows the profit percentage of sales for each product category. Which formula correctly calculates this?

hard

A. AVG([Profit]) / AVG([Sales])

B. SUM([Profit] / [Sales]) * 100

C. SUM([Profit]) - SUM([Sales]) * 100

D. SUM([Profit]) / SUM([Sales]) * 100

Solution

1. Step 1: Understand profit percentage calculation

   Profit percentage is (total profit / total sales) times 100 to get percent.

2. Step 2: Use correct aggregation and order of operations

   Sum profit and sales separately, divide sums, then multiply by 100.

3. Final Answer:

   SUM([Profit]) / SUM([Sales]) * 100 -> Option D

4. Quick Check:

   Profit % = (SUM(Profit) / SUM(Sales)) * 100 [OK]

Hint: Divide summed profit by sales, multiply by 100 for percent [OK]

Common Mistakes:

• Dividing fields before summing causing wrong results
• Using AVG instead of SUM for totals
• Subtracting instead of dividing