Percent of total in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to understand what percentage each product category contributes to the total sales for the last quarter.

📊 Data: You have sales data with columns: Order Date, Product Category, Sales Amount.

🎯 Deliverable: Create a Tableau dashboard showing sales by product category and the percent of total sales each category represents.

Sample Data

Order Date	Product Category	Sales Amount
2024-01-15	Electronics	1200
2024-01-20	Furniture	800
2024-02-05	Electronics	1500
2024-02-18	Office Supplies	400
2024-03-10	Furniture	700
2024-03-15	Office Supplies	600
2024-03-20	Electronics	1300
2024-03-25	Furniture	900

1

Step 1: Connect your Tableau workbook to the sales data source containing Order Date, Product Category, and Sales Amount.

No formula needed; just connect the data source.

Expected Result

Data source is connected and visible in Tableau.

2

Step 2: Filter the data to include only sales from the last quarter (January to March 2024).

Create a filter on Order Date: Order Date >= '2024-01-01' AND Order Date <= '2024-03-31'.

Expected Result

Only sales records from Jan 1, 2024 to Mar 31, 2024 are included.

3

Step 3: Create a bar chart with Product Category on Rows and SUM(Sales Amount) on Columns.

Drag Product Category to Rows, drag Sales Amount to Columns, aggregation set to SUM.

Expected Result

Bar chart shows total sales for each product category.

4

Step 4: Create a calculated field to compute the percent of total sales for each product category.

Percent of Total = SUM([Sales Amount]) / TOTAL(SUM([Sales Amount]))

Expected Result

Calculated field 'Percent of Total' is created.

5

Step 5: Add the 'Percent of Total' calculated field to the Label shelf to show percentage on each bar.

Drag 'Percent of Total' to Label, format as percentage with one decimal place.

Expected Result

Each bar shows the percent of total sales for that category.

6

Step 6: Format the chart with a clear title: 'Sales by Product Category and Percent of Total - Q1 2024'.

Add title and adjust colors for clarity and accessibility.

Expected Result

Dashboard is clear, readable, and visually accessible.

Final Result

Sales by Product Category and Percent of Total - Q1 2024

Electronics  | ██████████████  54.1%
Furniture    | ████████        32.4%
Office Supplies | ████          13.5%

✓Electronics contributed the highest sales at 54.1% of total sales.

✓Furniture made up 32.4% of total sales.

✓Office Supplies accounted for 13.5% of total sales.

Bonus Challenge

Create a trend line showing how the percent of total sales for each product category changes month by month in the quarter.

Show Hint

Use a line chart with Month on Columns, Product Category on Color, and the Percent of Total calculated field on Rows.

Practice

1. What does the "Percent of total" calculation show in Tableau?

easy

A. Each part's share of the whole

B. The total sum of all measures

C. The difference between two measures

D. The average value of a measure

Solution

1. Step 1: Understand the meaning of percent of total

   Percent of total shows how much each part contributes to the entire total.

2. Step 2: Relate to Tableau usage

   In Tableau, this is used to see the share of each category or item compared to the whole dataset.

3. Final Answer:

   Each part's share of the whole -> Option A

4. Quick Check:

   Percent of total = part / whole [OK]

Hint: Percent of total means part divided by whole [OK]

Common Mistakes:

• Confusing percent of total with sum
• Thinking it shows difference instead of share
• Mixing average with percent of total

2. Which of the following is the correct Tableau formula to calculate percent of total for a measure named Sales?

easy

A. SUM([Sales]) / TOTAL(SUM([Sales]))

B. TOTAL(SUM([Sales])) / SUM([Sales])

C. SUM([Sales]) * TOTAL(SUM([Sales]))

D. SUM([Sales]) - TOTAL(SUM([Sales]))

Solution

1. Step 1: Identify the correct formula structure

   The percent of total is calculated by dividing the sum of the part by the total sum.

2. Step 2: Match with Tableau syntax

   In Tableau, TOTAL() wraps the aggregation to get the grand total, so SUM([Sales]) / TOTAL(SUM([Sales])) is correct.

3. Final Answer:

   SUM([Sales]) / TOTAL(SUM([Sales])) -> Option A

4. Quick Check:

   Part / Total = SUM / TOTAL(SUM) [OK]

Hint: Divide SUM by TOTAL(SUM) for percent of total [OK]

Common Mistakes:

• Reversing numerator and denominator
• Using multiplication instead of division
• Subtracting instead of dividing

3. Given a dataset with categories A=100, B=300, and C=600 sales, what is the percent of total for category B using SUM([Sales]) / TOTAL(SUM([Sales]))?

medium

A. 10%

B. 30%

C. 60%

D. 50%

Solution

1. Step 1: Calculate total sales

   Total sales = 100 + 300 + 600 = 1000.

2. Step 2: Calculate percent of total for category B

   Percent = 300 / 1000 = 0.3 or 30%.

3. Final Answer:

   30% -> Option B

4. Quick Check:

   300 / 1000 = 0.3 = 30% [OK]

Hint: Divide category value by total sum, then convert to percent [OK]

Common Mistakes:

• Adding instead of dividing
• Using wrong total value
• Confusing category values

4. You wrote the formula SUM([Sales]) / TOTAL(SUM(Sales)) but Tableau shows an error. What is the problem?

medium

A. Using SUM instead of AVG

B. Division operator is incorrect

C. TOTAL function cannot be used with SUM

D. Missing square brackets around Sales in TOTAL function

Solution

1. Step 1: Check syntax of field references

   In Tableau, field names must be enclosed in square brackets inside functions.

2. Step 2: Identify missing brackets in TOTAL function

   The formula uses TOTAL(SUM(Sales)) but should be TOTAL(SUM([Sales])).

3. Final Answer:

   Missing square brackets around Sales in TOTAL function -> Option D

4. Quick Check:

   Field names need [ ] in functions [OK]

Hint: Always use [ ] around field names inside functions [OK]

Common Mistakes:

• Omitting brackets around field names
• Using wrong aggregation functions
• Incorrect operator usage

5. You want to show the percent of total sales by region in a bar chart. Which steps correctly apply this in Tableau?

hard

A. Create a calculated field: TOTAL(SUM([Sales])) / SUM([Sales]), then use Region on Rows

B. Use SUM([Sales]) only, then sort bars descending

C. Create a calculated field: SUM([Sales]) / TOTAL(SUM([Sales])), format as percentage, then use Region on Columns and this field on Rows

D. Create a calculated field: SUM([Sales]) - TOTAL(SUM([Sales])), then use Region on Columns

Solution

1. Step 1: Write correct percent of total formula

   Use SUM([Sales]) / TOTAL(SUM([Sales])) to get each region's share.

2. Step 2: Build the visualization

   Place Region on Columns and the calculated percent field on Rows, then format as percentage for clarity.

3. Final Answer:

   Create a calculated field: SUM([Sales]) / TOTAL(SUM([Sales])), format as percentage, then use Region on Columns and this field on Rows -> Option C

4. Quick Check:

   Percent of total formula + proper chart setup [OK]

Hint: Divide SUM by TOTAL(SUM), format %, use region on axis [OK]

Common Mistakes:

• Using subtraction instead of division
• Reversing numerator and denominator
• Not formatting as percentage