KNOWLEDGE BASE

Calculating Percent Of Total Without Using Table Calculations


Published: 05 Aug 2016
Last Modified Date: 08 May 2019

Environment

Tableau Desktop

Answer

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
  1. Drag [Region] to the Rows shelf
  2. Drag [Segment] to Color on the Marks card
  3. Drag [Sales] to the Columns shelf
  4. Right-click [Sales] on the Columns shelf and select Quick Table Calculation > Percent of total
  5. Right-click [Sales] on the Columns shelf and select Compute Using > Table (across)
  6. 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
  1. Select Analysis > Create Calculated Field
  2. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "Ex 1: % of total sales per segment within region"
    2. Drag the green SUM(Sales) field with the triangle icon from the Marks card into the calculation editor
    3. Tableau Desktop will automatically write out the following calculation: 
      SUM([Sales])/TOTAL(SUM([Sales]))
  3. 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.
  4. Replace SUM(Sales) in the view with [Ex 1: % of total sales per segment within region]
  5. (Optional) Modify the Axis' default numbers to Percentage by formatting it. 
To view the steps showed in the below video, please expand the above section.
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
See the worksheet "Example 2: LOD Scope Variations" to view these examples. See the additional notes section of this article for tips on when to use EXCLUDE vs FIXED.
To view the steps showed in the video below, please expand the above section.
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:
{ 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:
  1. 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]) }
  2. Right-click Percent of Total in the Measures pane and select Default Properties Number Format... > Percentage
  3. Place Percent of Total on Columns
  4. Place Region on Rows
  5. Place Segment on Color.
  6. Place Percent of Total on Label.
To view the above steps, please see the video below.
Note: the video has no sound.

To view an additional example, see the worksheet "Example 3: Working with Filters" in the attached workbook.
  1. 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]) }
  2. Drag [Ex 3: % of total sales per segment within region (not filtered)] to the Columns shelf
  3. Drag [Category] and [Sub-Category] to the Filters shelf
  4. 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:
  1. Either FIXED or INCLUDE must be used if the final view is missing dimensions needed to compute the numerator and/or denominator by;
  2. 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
  1. Right-click and drag [Order Date] to Columns shelf
  2. In the Drop Field dialog, select the green Order Date (continuous) and click OK
  3. Drag [Sales] to the Rows shelf
  4. Drag [Order ID] to Detail on Marks card
Step 2 - Create the calculated field
  1. 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])}
  2. 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
  3. Drag [Ex 4: Top Customers per Region] to Color on the Marks card
This example  can also be seen in the attached workbook, specifically in the sheets names "Example 4". 
To view the steps showed in the below video, please expand the above section.
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.
For more information about how table functions and LOD calculations are different, see Choosing the Right Calculation Type
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.
For more information about the exactly how LODs work, and the differences between FIXED, INCLUDE, and EXCLUDE, see Adding a Level of Detail Expression to the View


Discuss this article... Feedback Forum
Did this article resolve the issue?