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.