Pareto analysis in Tableau - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to identify the top products that contribute to 80% of total sales to focus marketing efforts.

📊 Data: You have monthly sales data for various products including Product Name, Sales Amount, and Month.

🎯 Deliverable: Create a Pareto chart in Tableau showing products ranked by sales contribution and cumulative percentage to highlight the vital few products.

Sample Data

Product Name	Sales Amount	Month
Alpha	12000	January
Beta	8000	January
Gamma	6000	January
Delta	4000	January
Epsilon	3000	January
Zeta	2000	January
Eta	1000	January
Theta	500	January

1

Step 1: Connect your sales data to Tableau and open a new worksheet.

No formula needed.

Expected Result

Data is loaded and ready for analysis.

2

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

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

Expected Result

Bar chart shows total sales per product.

3

Step 3: Sort the products in descending order by SUM(Sales Amount).

Right-click 'Product Name' in Rows, choose Sort, sort by Field, select SUM(Sales Amount), descending.

Expected Result

Bars are ordered from highest to lowest sales.

4

Step 4: Create a calculated field named 'Running Total Sales' to calculate cumulative sales.

RUNNING_SUM(SUM([Sales Amount]))

Expected Result

Calculated field computes cumulative sales as you move down the product list.

5

Step 5: Create a calculated field named 'Total Sales' to get total sales for all products.

WINDOW_SUM(SUM([Sales Amount]))

Expected Result

Calculated field shows total sales across all products.

6

Step 6: Create a calculated field named 'Cumulative Percentage' to find cumulative sales percentage.

[Running Total Sales] / [Total Sales]

Expected Result

Calculated field shows cumulative sales as a percentage of total sales.

7

Step 7: Add 'Cumulative Percentage' to the Columns shelf next to SUM(Sales Amount) to create a dual-axis chart.

Drag 'Cumulative Percentage' to Columns, right-click and select Dual Axis.

Expected Result

Chart shows bars for sales and a line for cumulative percentage.

8

Step 8: Synchronize the axes and format the 'Cumulative Percentage' axis as percentage.

Right-click on the right axis, choose Synchronize Axis, then format to Percentage with 0 decimals.

Expected Result

Axes are aligned and cumulative percentage is easy to read.

9

Step 9: Add a reference line at 80% on the cumulative percentage axis to highlight the Pareto threshold.

Right-click on cumulative percentage axis, Add Reference Line, set value to 0.8, label '80% Threshold'.

Expected Result

Reference line appears at 80% to identify vital few products.

10

Step 10: Format the chart with clear titles, axis labels, and tooltips for accessibility and clarity.

Set chart title to 'Pareto Analysis of Product Sales', label axes as 'Product' and 'Sales / Cumulative %'.

Expected Result

Final Pareto chart is clear, accessible, and ready to present.

Final Result

Product Sales Pareto Chart

| Alpha  | ██████████████  | 33% |
| Beta   | █████████       | 55% |
| Gamma  | ██████          | 71% |
| Delta  | ████            | 82% | <-- 80% Threshold
| Epsilon| ███             | 90% |
| Zeta   | ██              | 96% |
| Eta    | █               | 99% |
| Theta  | ░               |100% |

Bars represent sales amount; line shows cumulative %.

✓Top 4 products (Alpha, Beta, Gamma, Delta) contribute about 82% of total sales.

✓These top 4 products exceed the 80% cumulative sales threshold.

✓Focus marketing on these top 4 products to maximize impact.

Bonus Challenge

Create a Pareto analysis that updates dynamically by month using a filter.

Show Hint

Use Tableau's filter shelf to add 'Month' filter and ensure calculated fields recalculate based on selected month.

Practice

1. What is the main purpose of Pareto analysis in Tableau?

easy

A. To identify the few key items that cause most of the effect

B. To create detailed pie charts for all categories

C. To calculate averages of all data points

D. To filter out all data except the top item

Solution

1. Step 1: Understand Pareto analysis concept

   Pareto analysis focuses on the vital few items that contribute most to an outcome, often called the 80/20 rule.

2. Step 2: Relate to Tableau usage

   In Tableau, Pareto analysis helps highlight these key items by sorting and showing cumulative impact visually.

3. Final Answer:

   To identify the few key items that cause most of the effect -> Option A

4. Quick Check:

   Pareto analysis = Identify key items [OK]

Hint: Remember 80/20 rule means few items cause most effect [OK]

Common Mistakes:

• Thinking it calculates averages
• Assuming it filters to only one item
• Confusing it with pie chart creation

2. Which Tableau calculation is essential to create a Pareto chart showing cumulative impact?

easy

A. RANK(SUM([Quantity]))

B. SUM([Sales]) / COUNT([Orders])

C. AVG([Profit])

D. RUNNING_SUM(SUM([Sales]))

Solution

1. Step 1: Identify calculation for running total

   RUNNING_SUM(SUM([Sales])) computes a running total across sorted data, key for cumulative impact.

2. Step 2: Compare other options

   Other options calculate averages, ranks, or ratios, not cumulative sums needed for Pareto.

3. Final Answer:

   RUNNING_SUM(SUM([Sales])) -> Option D

4. Quick Check:

   Running total = RUNNING_SUM(SUM([Sales])) [OK]

Hint: Use RUNNING_SUM for running totals in Tableau [OK]

Common Mistakes:

• Using AVG instead of running sum
• Confusing rank with cumulative sum
• Dividing sums incorrectly

3. Given this Tableau calculation for cumulative percentage:
RUNNING_SUM(SUM([Sales])) / TOTAL(SUM([Sales]))
What is the output for the first sorted item with Sales = 100 and total Sales = 500?

medium

A. 100

B. 0.2

C. 1.0

D. 0.5

Solution

1. Step 1: Calculate running sum for first item

   Running sum for first item is SUM([Sales]) = 100.

2. Step 2: Calculate total sum and divide

   Total sum is 500, so cumulative percentage = 100 / 500 = 0.2 (20%).

3. Final Answer:

   0.2 -> Option B

4. Quick Check:

   100/500 = 0.2 [OK]

Hint: Divide running sum by total sum for cumulative percent [OK]

Common Mistakes:

• Using total sum as numerator
• Confusing running sum with total sum
• Not converting to percentage

4. You created a Pareto chart but the cumulative percentage line is not showing correctly. Which fix is most likely needed?

medium

A. Remove sorting on the dimension and use default order

B. Replace RUNNING_SUM with SUM([Sales]) only

C. Change calculation to use RUNNING_SUM(SUM([Sales])) / TOTAL(SUM([Sales])) and set table calculation to compute along sorted dimension

D. Use AVG([Sales]) instead of SUM([Sales]) in calculation

Solution

1. Step 1: Identify correct cumulative percentage formula

   RUNNING_SUM(SUM([Sales])) / TOTAL(SUM([Sales])) is the correct formula for cumulative percent.

2. Step 2: Ensure table calculation computes along sorted dimension

   Sorting is essential so running sum accumulates in correct order; setting compute using sorted dimension fixes line.

3. Final Answer:

   Change calculation to use RUNNING_SUM(SUM([Sales])) / TOTAL(SUM([Sales])) and set table calculation to compute along sorted dimension -> Option C

4. Quick Check:

   Correct formula + sorting = proper Pareto line [OK]

Hint: Always set table calc direction along sorted dimension [OK]

Common Mistakes:

• Using SUM instead of RUNNING_SUM
• Ignoring sorting order
• Using average instead of sum

5. You want to create a Pareto chart in Tableau showing top products contributing to 80% of sales. Which steps should you follow?

hard

A. Sort products by sales descending, calculate running sum of sales, compute cumulative percent, then combine bar and line charts

B. Filter top 80% products by sales, then create a pie chart of sales

C. Calculate average sales per product and highlight those above average

D. Sort products alphabetically, then plot sales as bars without cumulative calculations

Solution

1. Step 1: Sort products by descending sales

   Sorting ensures the largest contributors appear first for cumulative calculation.

2. Step 2: Calculate running sum and cumulative percent

   Use RUNNING_SUM(SUM([Sales])) and divide by TOTAL(SUM([Sales])) to get cumulative percent.

3. Step 3: Combine bar chart for sales and line chart for cumulative percent

   This visual combination clearly shows which products contribute to 80% of sales.

4. Final Answer:

   Sort products by sales descending, calculate running sum of sales, compute cumulative percent, then combine bar and line charts -> Option A

5. Quick Check:

   Sort + running sum + combo chart = Pareto analysis [OK]

Hint: Sort descending, running sum, cumulative %, then combo chart [OK]

Common Mistakes:

• Filtering before calculating cumulative percent
• Using average instead of cumulative sum
• Not combining bar and line charts