Custom table calculation scope in Tableau - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to understand the percentage contribution of each product category to total sales within each region and also across all regions.

📊 Data: You have sales data with columns: Region, Product Category, and Sales Amount.

🎯 Deliverable: Create a Tableau dashboard showing sales by product category and region, with two custom table calculations: one showing percentage of sales within each region, and another showing percentage of sales across all regions.

Sample Data

Region	Product Category	Sales Amount
North	Electronics	1000
North	Clothing	500
North	Home Goods	300
South	Electronics	800
South	Clothing	700
South	Home Goods	400
East	Electronics	1200
East	Clothing	600
East	Home Goods	500
West	Electronics	900
West	Clothing	400
West	Home Goods	350

1

Step 1: Connect your sales data to Tableau and drag Region to Rows, Product Category to Columns, and SUM(Sales Amount) to Text on the Marks card.

No formula needed; just drag and drop fields.

Expected Result

A table showing total sales for each product category by region.

2

Step 2: Create a calculated field named 'Percent of Sales by Region' to show each product category's sales as a percentage of total sales within its region.

SUM([Sales Amount]) / TOTAL(SUM([Sales Amount])) with Compute Using set to 'Product Category' and Scope set to 'Pane (Down)'.

Expected Result

Each product category's sales shown as a percentage of total sales in its region.

3

Step 3: Adjust the table calculation scope for 'Percent of Sales by Region' to compute percentages within each region only.

Edit Table Calculation: Set 'Compute Using' to 'Product Category' and 'Restarting every' to 'Region'.

Expected Result

Percentages reset for each region, summing to 100% per region.

4

Step 4: Create another calculated field named 'Percent of Sales Overall' to show each product category's sales as a percentage of total sales across all regions.

SUM([Sales Amount]) / TOTAL(SUM([Sales Amount])) with Compute Using set to 'Table (Down)'.

Expected Result

Each product category's sales shown as a percentage of total sales across all regions.

5

Step 5: Add both calculated fields to the view as additional columns next to sales amounts.

Drag 'Percent of Sales by Region' and 'Percent of Sales Overall' to the Columns shelf or Text on Marks card.

Expected Result

Dashboard shows sales, percent by region, and percent overall side by side.

6

Step 6: Format the percentage fields to show as percentages with one decimal place for clarity.

Right-click the calculated fields, select Format, and set Number Format to Percentage with 1 decimal place.

Expected Result

Percentages displayed clearly, easy to read.

Final Result

Region | Electronics | Clothing | Home Goods | % by Region (Electronics) | % by Region (Clothing) | % by Region (Home Goods) | % Overall (Electronics) | % Overall (Clothing) | % Overall (Home Goods)
-------------------------------------------------------------------------------------------------------------
North  | 1000        | 500      | 300        | 55.6%                    | 27.8%                  | 16.7%                   | 25.6%                   | 22.7%                 | 19.4%
South  | 800         | 700      | 400        | 42.1%                    | 36.8%                  | 21.1%                   | 20.5%                   | 31.8%                 | 25.8%
East   | 1200        | 600      | 500        | 52.2%                    | 26.1%                  | 21.7%                   | 30.8%                   | 27.3%                 | 32.3%
West   | 900         | 400      | 350        | 54.5%                    | 24.2%                  | 21.2%                   | 23.1%                   | 18.2%                 | 22.6%

✓Electronics is the top-selling category in all regions except South where Clothing is close.

✓Percentage of sales by region shows how product categories contribute differently within each region.

✓Percentage of sales overall highlights Electronics as the largest contributor company-wide.

Bonus Challenge

Create a parameter to let users switch the table calculation scope dynamically between 'Region' and 'All Regions' for the percentage calculation.

Show Hint

Use a parameter with values 'By Region' and 'Overall' and use an IF statement in the calculated field to change the table calculation scope based on the parameter selection.

Practice

1. What does the Custom Table Calculation Scope control in Tableau?

easy

A. Which data points are included in the calculation

B. The color scheme of the visualization

C. The font size of labels

D. The data source connection type

Solution

1. Step 1: Understand the purpose of table calculation scope

   The scope defines which data points Tableau uses when performing calculations like running totals or rankings.

2. Step 2: Identify what is controlled by scope

   It controls the subset of data included in the calculation, not visual formatting or data source settings.

3. Final Answer:

   Which data points are included in the calculation -> Option A

4. Quick Check:

   Scope controls data points included [OK]

Hint: Scope means which data points calculation uses [OK]

Common Mistakes:

• Confusing scope with visual formatting
• Thinking scope changes data source
• Mixing scope with filter settings

2. Which option correctly sets the scope of a table calculation to compute using only the Region dimension in Tableau?

easy

A. Compute Using > Pane (Across)

B. Compute Using > Table (Down)

C. Compute Using > Region

D. Compute Using > Table (Across)

Solution

1. Step 1: Identify the dimension to set scope

   To limit calculation to the Region dimension, select Compute Using > Region.

2. Step 2: Understand Compute Using options

   Options like Table (Down) or Table (Across) use the entire table direction, not a specific dimension.

3. Final Answer:

   Compute Using > Region -> Option C

4. Quick Check:

   Compute Using dimension name sets scope [OK]

Hint: Choose dimension name under Compute Using for scope [OK]

Common Mistakes:

• Selecting Table (Down) instead of dimension
• Confusing Pane with Table scope
• Not selecting specific dimension

3. Given a sales table with dimensions Region and Category, what will be the result of a running total table calculation with scope set to Compute Using: Category for Region 'West'?

medium

A. Running total sums all data ignoring Region and Category

B. Running total resets for each Category within the West region

C. Running total resets for each Region ignoring Category

D. Running total sums all Categories across all Regions

Solution

1. Step 1: Understand scope set to Category

   Compute Using Category means the running total computes along the Category dimension, restarting (partitioned) by other dimensions like Region.

2. Step 2: Consider Region filter 'West'

   Filtered to West, the running total resets for each Category within the West region.

3. Final Answer:

   Running total resets for each Category within the West region -> Option B

4. Quick Check:

   Compute Using Category: along Category, reset by Region [OK]

Hint: Compute Using sets addressing (along), partition sets reset [OK]

Common Mistakes:

• Thinking running total sums all regions
• Ignoring filter on Region
• Confusing scope with partitioning

4. You set a table calculation to Compute Using: Table (Across) but the running total is incorrect. What is the most likely cause?

medium

A. The table calculation is not enabled

B. The calculation is missing a filter

C. The data source is not connected

D. The data is arranged vertically, but scope is set horizontally

Solution

1. Step 1: Analyze Compute Using Table (Across)

   This scope computes calculation across columns (horizontally).

2. Step 2: Check data arrangement

   If data is arranged vertically (down rows), computing across columns causes incorrect results.

3. Final Answer:

   The data is arranged vertically, but scope is set horizontally -> Option D

4. Quick Check:

   Scope direction must match data layout [OK]

Hint: Match Compute Using direction to data layout [OK]

Common Mistakes:

• Ignoring data layout when setting scope
• Assuming filters cause running total errors
• Thinking data source connection affects calculation

5. You want to create a running total of sales that restarts for each Region and sums across Category. Which custom table calculation scope should you use?

hard

A. Compute Using: Category, Restarting every Region

B. Compute Using: Region, Restarting every Category

C. Compute Using: Table (Down)

D. Compute Using: Pane (Across)

Solution

1. Step 1: Understand requirement

   Running total should sum across Category but restart for each Region.

2. Step 2: Set scope to Category and partition by Region

   Setting Compute Using to Category calculates running total across categories. Restarting every Region means partitioning by Region.

3. Step 3: Confirm other options

   Other options do not correctly partition or compute as required.

4. Final Answer:

   Compute Using: Category, Restarting every Region -> Option A

5. Quick Check:

   Scope by Category with restart per Region [OK]

Hint: Compute Using dimension to sum, partition to restart [OK]

Common Mistakes:

• Confusing which dimension to compute using
• Not setting restart partition correctly
• Using Table (Down) which sums entire table