Pareto analysis in Tableau - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate cumulative percentage for Pareto analysis

You have a sales dataset with categories and sales amounts. Which Tableau calculated field formula correctly computes the cumulative percentage of total sales by category for a Pareto chart?

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

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

C RUNNING_SUM(SUM([Sales])) / TOTAL(SUM([Sales]))

D TOTAL(SUM([Sales])) / RUNNING_SUM(SUM([Sales]))

💡 Hint

Think about how to accumulate sales and then divide by total sales to get a percentage.

The correct formula uses RUNNING_SUM to accumulate sales by category in order, then divides by the total sales using TOTAL to get the cumulative percentage. This is essential for Pareto analysis to show the cumulative contribution.

❓ visualization

intermediate

Best visualization for Pareto analysis

Which visualization best represents a Pareto analysis in Tableau to show the 80/20 rule clearly?

A A bar chart sorted descending by category sales with a line chart overlay showing cumulative percentage

B A pie chart showing sales percentage by category

C A scatter plot of sales vs. category

D A stacked bar chart showing sales by category and region

💡 Hint

Pareto analysis combines individual values and their cumulative impact.

The classic Pareto chart combines bars sorted by descending value with a line showing cumulative percentage. This helps identify the vital few categories contributing most of the sales.

❓ data_modeling

advanced

Data preparation for Pareto analysis with multiple dimensions

You want to perform Pareto analysis on sales by product category and region. What is the best data modeling approach in Tableau to enable this?

A Create separate data sources for category and region and blend them in Tableau

B Use a pivoted data source with sales as columns and categories as rows

C Aggregate sales by category only and ignore region for Pareto analysis

D Use a single data source with category, region, and sales columns; then create calculated fields for cumulative sales by category and region combined

💡 Hint

Think about how to analyze combined dimensions in one dataset.

Having all relevant dimensions and measures in one data source allows Tableau to calculate cumulative sales correctly across combined categories and regions for Pareto analysis.

🔧 Formula Fix

advanced

Identify the error in Pareto cumulative calculation

A Tableau user wrote this calculated field for cumulative sales percentage: RUNNING_SUM(SUM([Sales])) / SUM([Sales]). What is the issue with this formula?

Tableau

RUNNING_SUM(SUM([Sales])) / SUM([Sales])

A RUNNING_SUM cannot be used with SUM aggregation

B It divides cumulative sales by the current row sales, not total sales, causing incorrect percentages

C The formula is correct and will produce the expected cumulative percentage

D SUM([Sales]) should be replaced with TOTAL(SUM([Sales])) to fix syntax

💡 Hint

Check what the denominator represents in the formula.

The denominator SUM([Sales]) returns the sales for the current row only, not the total sales. For cumulative percentage, the denominator must be the total sales across all rows, which requires TOTAL(SUM([Sales])).

🧠 Conceptual

expert

Understanding Pareto principle application in BI

In a Pareto analysis dashboard, you notice that 30% of categories contribute to 90% of sales. What does this imply about your data and business focus?

A A small number of categories generate most sales, so focusing on them can improve business efficiency

B Sales are evenly distributed across all categories, so no category is more important

C The data is incorrect because Pareto always shows 80/20 split

D You should ignore the top categories and focus on the remaining 70% for growth

💡 Hint

Pareto principle means a few causes create most effects.

The Pareto principle states that a small percentage of causes (categories) often account for a large percentage of effects (sales). This insight helps prioritize business efforts on the vital few.

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