Last Modified Date: 20 Jul 2023
Environment
Tableau DesktopAnswer
General Resolution
Create a calculation that uses a level of detail (LOD) function in the denominator to find the total. For example, to find the percent of total sales per segment; the following table calculation for finding the percent of total sales:SUM([Sales]) / TOTAL(SUM([Sales]))
Could become:
SUM([Sales]) / SUM( { EXCLUDE [Segment] : SUM([Sales]) } )
CLICK TO EXPAND SOLUTION
Example 1: Convert a quick table calculation into a LOD calculation
These directions start from the "Example 1: table calc" worksheet in the attached workbook.
Step 1 - Build the view with a table calculation
- Drag [Region] to the Rows shelf
- Drag [Segment] to Color on the Marks card
- Drag [Sales] to the Columns shelf
- Right-click [Sales] on the Columns shelf and select Quick Table Calculation > Percent of total
- Right-click [Sales] on the Columns shelf and select Compute Using > Table (across)
- Ctrl + drag [Sales] from the Columns shelf to Label on the Marks card. This will create a copy with the same quick table calculation applied.
Step 2 - Reproduce the same result with a calculated field
- Select Analysis > Create Calculated Field
- In the Calculated Field dialog box that opens, do the following, and then click OK:
- Name the calculated field. In this example, the calculated field is named "Ex 1: % of total sales per segment within region"
- Drag the green SUM(Sales) field with the triangle icon from the Marks card into the calculation editor
- Tableau Desktop will automatically write out the following calculation:
SUM([Sales])/TOTAL(SUM([Sales]))
- Modify the calculation to replace the table function TOTAL() with a LOD function. The final calculation may look like:
SUM([Sales])/SUM({ EXCLUDE [Segment] : SUM([Sales])})
Important: The EXCLUDE function will compute an expression, in this case SUM([Sales]), as if the dimension(s) listed after EXCLUDE had been removed from the view. The SUM() outside of EXCLUDE is required to avoid the "cannot mix aggregate and nonaggregate arguments" error and does not change the value of the denominator. - Replace SUM(Sales) in the view with [Ex 1: % of total sales per segment within region]
- (Optional) Modify the Axis' default numbers to Percentage by formatting it.
Note: the video has no sound.
CLICK TO EXPAND STEPS
Example 2: Adjusting the scope of the percent of total
Option 1
If using EXCLUDE, list all dimension after EXCLUDE except for the ones that will group the measure values. For example, if the view includes [Region], [Segment], and [Category], and the goal is to find the percent of total for each segment within each region, then the calculation would look like:{ EXCLUDE [Category] : SUM( [Sales] ) }/{ EXCLUDE [Segment], [Category] : SUM( [Sales] ) }
If using FIXED, the list all dimensions after FIXED that will be used to group the measure values. The same example above using FIXED would be:
{ FIXED [Segment], [Region] : SUM( [Sales] ) }/{ FIXED [Region] : SUM( [Sales] }
Option 2
To find the percentage of total Sales per Segment overall, you will notice that this specific example will show Consumer at 50.56% for each region and every Category. This is because the percent of total is tied to the segment, and as there are additional dimension in the view, the percentage will be repeated.{ EXCLUDE [Region], [Category] : SUM([Sales]) }/{ EXCLUDE [Region], [Category], [Segment] : SUM([Sales])}
If using FIXED, use the following equivalent calculation:
{ FIXED [Segment] : SUM([Sales]) }/{ FIXED : SUM([Sales]) }
Option 3
Use the following calculation to view the percentage of total Sales for every row in the view.SUM([Sales])/SUM({ EXCLUDE [Category] : SUM([Sales]) })
- SUM([Sales]) will be calculated for every row in the view.
- The EXCLUDE statement will calculated sales for every row in the view as if [Category] were removed.
- IIf additional dimensions are added to the view, then the values of the numerator and denominator will change accordingly
Note: the video has no sound.
CLICK TO EXPAND STEPS
Example 3: Including or ignoring filters
Due to the order of operations, FIXED expressions are only filtered by context filters. EXCLUDE expressions are filtered by all dimension filters.
Instead of table calculations, or using the TOTAL() function, use Level of Detail (LOD) expressions such as:
For example, using Superstore sample data, to show what percent of total each Segment is per Region:
Note: the video has no sound.
To view an additional example, see the worksheet "Example 3: Working with Filters" in the attached workbook.
Instead of table calculations, or using the TOTAL() function, use Level of Detail (LOD) expressions such as:
{ FIXED [<dimension(s) you are looking for the percentage of total information for>] : SUM([Measure]) } /
{ FIXED [<"Total" dimension(s)>] : SUM([Measure]) }
For example, using Superstore sample data, to show what percent of total each Segment is per Region:
- Select Analysis > Create Calculated Field. Name the calculation Percent of Total, enter the following calculation, and click OK:
{ FIXED [Segment], [Region] : SUM([Sales]) }/{ FIXED [Region] : SUM([Sales]) }
- Right-click Percent of Total in the Measures pane and select Default Properties > Number Format... > Percentage
- Place Percent of Total on Columns.
- Place Region on Rows.
- Place Segment on Color.
- Place Percent of Total on Label.
Note: the video has no sound.
To view an additional example, see the worksheet "Example 3: Working with Filters" in the attached workbook.
- Create a calculated field with a name like "Ex 3: % of total sales per segment within region (not filtered)" with a calculation similar to the following:
{ FIXED [Segment], [Region] : SUM([Sales]) }/{ FIXED [Region] : SUM([Sales]) } - Drag [Ex 3: % of total sales per segment within region (not filtered)] to the Columns shelf
- Drag [Category] and [Sub-Category] to the Filters shelf
- Right-click [Category] on the Filters shelf and select Add to Context
Explanations of the above calculation:
- The calculation using EXCLUDE is filtered by both the [Category] and [Sub-Category] filters (this would also be true for table functions)
- The calcultion using FIXED is only filtered by [Catgory] because that filter has been added to context.
- It is possible to mix FIXED and EXCLUDE in one calculation so that one piece is filtered and another not filtered.
CLICK TO EXPAND STEPS
Example 4: Using the percent of total in another calculation
When using a percent of total calculation in another calculation, there are two main considerations:
- Either FIXED or INCLUDE must be used if the final view is missing dimensions needed to compute the numerator and/or denominator by;
- The percent of total calculation needs to match the aggregation of the calculation it is used in. LODs always return non-aggregate values.
Step 1 - Build the View
- Right-click and drag [Order Date] to Columns shelf
- In the Drop Field dialog, select the green Order Date (continuous) and click OK
- Drag [Sales] to the Rows shelf
- Drag [Order ID] to Detail on Marks card
Step 2 - Create the calculated field
- Create a calculated field with a name like "Ex 4: % of total per customer overall" with a calculation similar to the following:
{ FIXED [Customer Name], [Region] : SUM([Sales]) }/{FIXED [Region] : SUM([Sales])} - Create a calculated field with a name like "Ex 4: Top Customers per Region" with a calculation similar to the following:
IF
([Ex 4: % of total per customer within region] > .02
AND [Region] = "South")
OR
[Ex 4: % of total per customer within region] > .0075
THEN "big customer"
ELSE "other"
END - Drag [Ex 4: Top Customers per Region] to Color on the Marks card
Note: the video has no sound.
Additional Information
Table Calculations vs LOD
Table functions and LOD functions both allow us to determine the scope of the total. Depending on how the percent of total is being used, one choice may be better suited:- Table calculations may show unexpected results in totals
- Table calculation may be altered unexpectedly by filters
- FIXED LOD calculations ignore all filters except for context filters
- A calculation referencing the percent of total may encounter the "cannot mix aggregate and nonaggreate arguments". Use the percent of total calculation that matches the aggregation of the calculation it is going into. FIXED expressions are non-aggregated, while EXCLUDE and table functions are aggregate.
EXCLUDE vs FIXED
In most cases, either EXCLUDE or FIXED can be used. Some factors to consider:
- EXCLUDE statements will respect all dimension filters. FIXED will only respect context filters.
- EXCLUDE statements must be aggregated in the view or on the Filters shelf. FIXED statements are treated like dimensions.
- These examples only show EXCLUDE and not INCLUDE for simplicity. INCLUDE will compute an expression as if the specified dimension(s) were added to the view. INCLUDE is a more appropriate choice than EXCLUDE when the dimension is missing the dimensions needed to define the scope of the numerator and/or denominator.
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Continue Searching
Knowledge Base
Community
Product Help
Training and Tutorials