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]) } )

• Example 1: Convert a quick table calculation into a LOD calculation
• Example 2: Adjusting the scope of the percent of total
• Example 3: Including or ignoring filters
• Example 4: Using the percent of total in another calculation
  

  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.
•  

  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.
  
   

  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.

  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.