Table Calculations

Table Calculations are user-defined aggregate summaries of the underlying data in the Table View. These calculations either replace visible cell data with the results of their calculations or add additional rows or columns with the calculated results.

Visual Analytics offers the following types of Table Calculations

• Difference From
• % From
• % Difference From
• Running Total
• % Of Total
• Moving Calculation
• Rank
• Percentile

Table Calculations default to [Difference From] if no other option is chosen when they are created.

Table Across calculations perform their calculations from left to right across a table within a row. It is possible to focus on just a range rather than the entire row.

Let's say that you used the table calculation "% Of Total.." from the numbers below

Your total is 197.

Visual Analytics will traverse the row calculating the % of the total for each column. Below, we are highlighting with red to indicate the result of the calculation. You would see

So at a glance, you can see what the percentage of the total each value is.

Let's say that you used the table calculation "Difference From" and you decided that you want to look relative to the previous value.

Using the numbers supplied, you would see:

You will see the difference from one column to the next, looking back at the previous column.

The summarization is based on one of the calculations.

How to Create a Table Calculation
There are two ways using which you can apply table calculations from the drop-down list.

• Click Add Table Calculation. The Add Table Calculation dialog opens. Select your desired calculation type and other settings.
• Click Table Calculations Templates > click a calculation type. On selecting the desired calculation type, the calculation definition options are also displayed in the drop-down list. You can switch to a different calculation type or change the calculation definition without opening the Edit Table Calculation dialog.

This procedure instructs you how to apply table calculations to a measure, using the Table Calculations Templates method. 
To apply table calculation

1. Right-click a measure.
2. Click Table Calculation Templates > select the desired calculation type.
   
   
   The chart view will display the applied table calculation. If you want to change the calculation type, repeat steps 1 & 2.
3. To change the calculation definition, repeat steps 1 & 2, and then select the desired calculation definition from the drop-down.
   
4. Hover the mouse on the cell, to view summarized values in a tooltip.
   

   This  is displayed for the fields to which table calculations have been applied.

% of Total - Table Across

Table Across calculations perform their calculations from left to right across a table within a row. In the example below, green circles indicate values used in the [% of Total - Table Across] table calculation for SUM(Freight). Red squares indicate the results of the table calculation.

(1) In the first row, the red box around 18% for % of Total was arrived at by using the following calculation:
108.6 / 603.2
where 603.2 is the Grand Total SUM(Freight) for all items on this row. The value 603.2 is therefore 100% of SUM(Freight) for Currency Code AUD and Card Type ColonialVoice.

(2) Still in the first row, at the right side of the Bib-Shorts category, in its Total column, the red box around 20.49% was arrived at by using the following calculation:
123.61 / 603.2
where 123.61 is the SUM(Freight) for Currency Code AUD, Bib-Shorts Product Category and Sales Reasons of On Promotion and Price. 

(3) Within the Grand Total column at the far right of the first row, the red box around 100% indicates that 603.2 is 100% of the SUM(Freight) for this row.

(4) Within the fifth row labelled Total for AUD Currency Code, the red box around 27.18% was arrived at by using the following calculation:
668.17 / 2458.29
where 2458.29 is the Grand Total of all SUM(Freight) for Currency Code of AUD.

(5) Still within the fifth row labelled Total for AUD Currency Code, the red box around 48.1% was arrived at using the following calculation:
1182.54 / 2458.29

(6) The bottom row of the table, Grand Total, displays a red box around 18.84% in the Bib-Shorts/Price column. This was arrived at by using the following calculation:
538.71 / 2859.58
where 2859.58 is the Grand Total of all SUM(Freight) in the table.

(7) Still within the bottom row of the table, the red box around 48.24% was arrived at by using the following calculation:
1384.54 / 2859.58

(8) The value 2859.58 is the sum of every row's SUM(Freight) value, and therefore 100% of SUM(Freight) for the entire table.

% of Total - Summarize Values from [selected dimension: Sales Reason] Range [selected dimension: Product Category]

Table calculations that summarize values from a selected dimension within a range of a selected dimension will summarize only in the range and not across the entire table.

(1) In the first row, the red box around 12.143% for Percent of Total in SUM(Freight) was arrived at by using the following calculation:
15.01 / 123.61
where 123.61 is the SUM(Freight) Bib-Shorts/On Promotion for Currency Code AUD and Card Type ColonialVoice on this row.

(2) In the fifth row, the red box around 43.497% was arrived at by using the following calculation:
514.37 / 1182.54
where 1182.54 is the total of all Bib-Shorts/On Promotion for Currency Code AUD and all Card Types.

(3) In the last row of the table, the red box around 38.909% was arrived at by using the following calculation:
538.71 / 1384.54
where 1384.54 is the Grand Total of all Bib-Shorts/On Promotion for both Currency Codes and all Card Types.

(4) In the Bike Racks/Price column, the red box around 29.273% was arrived at by using the following calculation:
25.28 / 86.36
where 61.08 is the SUM(Freight) for the previous item (Bike Racks/On Promotion for the Currency Code CAD and Card Type SuperiorCard) on this row.

(5) In the last row of the table, the red box around 22.801% was arrived at by using the following calculation:
336.33 / 1475.04
where 1475.04 is the Grand Total of all Bike Racks/Price for both Currency Codes and all Card Types.

 

% of Total - Table Down

Table Down calculations perform their calculations from top to bottom down a table within a column. In the example below, green circles indicate values used in the [% of Total - Table Down] table calculation for SUM(Freight). Red squares indicate the results of the table calculation.

(1) In the first row, the red box around 12.839% for % of Total was arrived at by using the following calculation:
108.6 / 845.83
where 845.83 is the Grand Total SUM(Freight) for all items in this column. The value 845.83 is therefore 100% of SUM(Freight) for Bib-Shorts/On Promotion.

(2) In the fifth row, at the right side of the Bib-Shorts/On Promotion column, in its Total row, the red box around 78.996% was arrived at by using the following calculation:
668.17 / 845.83
where 668.17 is the SUM(Freight) for all of Bib-Shorts/On Promotion and Currency Code of  AUD.

(3) Within the Grand Total row at the bottom of the first column, the red box around 100% indicates that 845.83 is 100% of the SUM(Freight) for all Bib-Shorts/On Promotion.

(4) Within the row labelled Total for CAD Currency Code, the red box around 7.883% was arrived at by using the following calculation:
89.77 / 1138.71
where 1138.17 is the Grand Total of all SUM(Freight) for Bike Shorts/On Promotion with Currency Code of CAD.

(5) The value 2859.58 is the sum of every row's SUM(Freight) value, and therefore 100% of SUM(Freight) for the entire table.

% of Total - Summarize Values from [selected dimension: Card Type] Range [selected dimension: Currency Code]

Table calculations that summarize values from a selected dimension within a range of a selected dimension will summarize only in the range and not across the entire table. In this example below, green circles indicate the values used in the [% of Total - Card Type - range - Currency Code] table calculation for SUM(Freight). Red squares indicate the results of the table calculation.

(1) In the first row, the red box around 16.253% for % of Total was arrived at by using the following calculation:
108.6 / 668.17
where 668.17 is the combined sums of all the AUD Card Types On Promotion.

(2) In the fifth row, the red box around for 100% for % of Total was arrived at by summing all of the percentages for Currency Code AUD's Card Types

(3) In the Grand Total row at the bottom of the Bib-Shorts On Promotion column, the 100% was arrived at because 845.83 is 100% of all Bib-Shorts On Promotion for all Currency Codes and Card Types.

(4) In the Bike Racks Price column, the red box around 9.998% was arrived at using the following calculation:
10.95 / 109.52
where 109.52 is the sum of all Bike Racks Price of Currency Code CAD for all Card Types.

(5) The value 2859.58 is the sum of every row's SUM(Freight) value, and therefore 100% of SUM(Freight) for the entire table.

% From Previous - Table Across - Range: All

Table calculations that summarize values from Table (Across) will summarize across the entire table.

(1) In the first row, the red box around 13.821% for % Difference in SUM(Freight) was arrived at by using the following calculation:
15.01 / 108.6 = 13.821%
where 108.6 is the SUM(Freight) for the previous item (Bib-Shorts/On Promotion for Currency Code AUD and Card Type ColonialVoice) on this row.

(2) In the fifth row, the red box around 76.982% was arrived at by using the following calculation:
514.37 / 668.17 = 76.982%
where 668.17 is the total of all Bib-Shorts/On Promotion for Currency Code AUD and all Card Types.

(3) In the seventh row of the table, the red box around 0% was arrived at by using the following calculation:
0 / 9.21 = 0%
where 9.21 is the SUM(Freight) of all Bib-Shorts/Price for Currency Code CAD and Card Type Distinguish.
 
(4) In the final row within Bike Racks/On Promotion column, the red box around 211.377% was arrived at by using the following calculation:
1138.71 / 538.71 = 211.377%
where 538.71 is the SUM(Freight) for the previous item (Bib-Shorts/Price for all Currency Codes and all Card Types).

Difference From Previous - Summarize Values from [selected dimension: Sales Reason] Range [selected dimension: Product Category]

Table calculations that summarize values from a selected dimension within a range of a selected dimension will summarize only in the range and not across the entire table.

(1) In the first row, the red box around -93.59 for Difference in SUM(Freight) was arrived at by using the following calculation:
15.01 - 108.6
where 108.6 is the SUM(Freight) for the previous item (Bib-Shorts/On Promotion for Currency Code AUD and Card Type ColonialVoice) on this row.

(2) In the fifth row, the red box around -153.8 was arrived at by using the following calculation:
514.37 - 668.17
where 668.17 is the total of all Bib-Shorts/On Promotion for Currency Code AUD and all Card Types.

(3) In the last row of the table, the red box around -307.12 was arrived at by using the following calculation:
538.71 - 845.83
where 845.83 is the Grand Total of all Bib-Shorts/On Promotion for both Currency Codes and all Card Types.
 
(4) In the Bike Racks/Price column, the red box around -35.8 was arrived at by using the following calculation:
25.28 - 61.08
where 61.08 is the SUM(Freight) for the previous item (Bike Racks/On Promotion for the Currency Code CAD and Card Type SuperiorCard) on this row.

(5) In the last row of the table, the red box around -802.38 was arrived at by using the following calculation:
336.33 - 1138.71
where 1138.71 is the SUM(Freight) of all Bike Racks/On Promotion for both Currency Codes and all Card Types.

% Difference From Previous - Table Across - Range: All

Table calculations which summarize values from Table (Across) will summarize across the entire table.

(1) In the first row, the red box around -86.179% for % Difference in SUM(Freight) was arrived at by using the following calculation:
15.01 - 108.6 = -93.59
-93.59 / 108.6 = -86.179%
where 108.6 is the SUM(Freight) for the previous item (Bib-Shorts/On Promotion for Currency Code AUD and Card Type ColonialVoice) on this row.

(2) In the fifth row, the red box around -23.018% was arrived at by using the following calculation:
514.37 - 668.17 = -153.8
-153.8 / 668.17 = -23.018%
where 668.17 is the total of all Bib-Shorts/On Promotion for Currency Code AUD and all Card Types.

(3) In the seventh row of the table, the red box around -100% was arrived at by using the following calculation:
0 - 111.67 = -111.67
-111.67 / 111.67 = -100%
where 0 is the SUM(Freight) of all Bib-Shorts/On Promotion for Currency Code CAD and Card Type AUD.
 
(4) In the final row within Bike Racks/Price column, the red box around -70.464% was arrived at by using the following calculation:
336.33 - 1138.71 = -802.38
-802.38 / 1138.71 = -70.464%
where 1138.71 is the SUM(Freight) for the previous item (Bike Racks/On Promotion for all Currency Codes and all Card Types).

Moving Calculation - Summarize Values using: [selected function: Average] - Summarize Values from: [selected dimension: Sales Reason] - Range: [Product Category]

Table calculations for Moving Calculation which summarize values using a selected function (Average, Sum, Minimum or Maximum) from a selected dimension will perform the Moving Calculation only in the selected range. In this case, our Range is [Product Category].

(1) In the first row, the red box around 62 for Moving Average of SUM(Freight) along Sales Reason was arrived at by using the following calculation:
109 + 15 / 2
where 109 is the SUM(Freight) for the previous item (Bib-Shorts/On Promotion for Currency Code AUD and Card Type ColonialVoice) on this row. The 2 is used in the calculation because we have selected the Average function, and there are 2 values.

(2) In the fifth row, the red box around 591 was arrived at by using the following calculation:
668 + 514 / 2
where 668 is the total of all Bib-Shorts/On Promotion for Currency Code AUD and all Card Types.  The 2 is used in the calculation because we have selected the Average function, and there are 2 values.

(3) In the last row of the table, the red box around 692 was arrived at by using the following calculation:
846 + 539 / 2
where 846 is the Grand Total of all Bib-Shorts/On Promotion for both Currency Codes and all Card Types.  The 2 is used in the calculation because we have selected the Average function, and there are 2 values.
 
(4) In the Bike Racks/Price column, the red box around 43 was arrived at by using the following calculation:
61 + 25 / 2
where 61 is the SUM(Freight) for the previous item (Bike Racks/On Promotion for the Currency Code CAD and Card Type SuperiorCard) on this row.  The 2 is used in the calculation because we have selected the Average function, and there are 2 values.

(5) In the last row of the table, the red box around 2860 was arrived at by using the following calculation:
1385 + 1475 / 2
where 1385 is the Grand Total of all Bib Shorts/On Promotion for both Currency Codes and all Card Types. The 2 is used in the calculation because we have selected the Average function, and there are 2 values.

Running Total - Summarize Values using: [Sales Reason] - Range: [All]

Table calculations for Running Total will perform a Running Total summarizing values from Sales Reason only in the selected range. In this case, our Range is [All].

(1) In the first row, the red box around 123 for Moving Average of SUM(Freight) along Sales Reason was arrived at by using the following calculation:
108.6 + 15.01
where 108.6 is the Running SUM(Freight) for the previous item (Bib-Shorts/On Promotion for Currency Code AUD and Card Type ColonialVoice) on this row. 

(2) In the first row still inside the Bike Racks/Price column, the red box around 603.2 was arrived at by using the following calculation:
512.58 + 90.62
where 512.58 is the Running Total of all Bike Racks/On Promotion for Currency Code AUD and Card Type ColonialVoice.  

(3) In the third row of the table, the red box around 645.17 was arrived at by using the following calculation:
305.8 + 339.37
where 305.8 is the Running Total of all Bib-Shorts Sales Reasons for both Currency Code AUD and Card Type SuperiorCard.  
 
(4) In the eighth row, Bike Racks Total column, the red box around 105.36 was arrived at by using the following calculation:
19 + 86.36
where 19 is the Total SUM(Freight) for all Bib-Shorts Sales Reasons for the Currency Code CAD and Card Type SuperiorCard on this row.

(5) In the last row of the table, the red box around 2859.58 was arrived at by using the following calculation:
1384.54 + 1475.04
where 1384.54 is the Grand Total of all Bib Shorts/On Promotion for both Currency Codes and all Card Types. 

Rank - Summarize Values from: [Table (Across)] - Sort Order: [Descending] - Rank Duplicate Values as: [Competition (1,2,2,4)]

Table Calculations for Rank will compare values and produce a Rank for each value. In this case our Sort Order is Descending and Duplicate Values are ranked as Competition (meaning two identical values will get the same rank). 

(1) In the first row, the red box around 2 for Rank was arrived at by comparing all of the values for SUM(Freight) on the Currency Code AUD and Card Type ColonialVoice row (excluding the Total and Grand Total columns). 109 is the second highest value on this row.

(2) In the third row, the red box around 3 for Rank was arrived at by comparing all of the values for SUM(Freight) on the Currency Code AUD and Card Type SuperiorCard row (excluding the Total and Grand Total columns). 142 is the third highest value on this row.

(3) In the fifth row, the red box around 2 for Rank was arrived at by comparing all of the Total values for SUM(Freight) on the Currency Code AUD row (excluding the Total and Grand Total columns). 668 is the second highest value on this row.

(4) In the seventh row, the red box around 1 for Rank was arrived at by comparing all of the values for SUM(Freight) on the Currency Code CAD and Card Type Distinguish row (excluding the Total and Grand Total columns). 102 is the highest value on this row.

(5) In the seventh row, the red box around 2 for Rank was arrived at by comparing all of the values for SUM(Freight) on the Currency Code CAD and Card Type Distinguish row (excluding the Total and Grand Total columns). 65 is the second highest value on this row.

(6) In the Grand Total row, the red box around 1 for Rank was arrived at by comparing all of the Grand Total row values (excluding the Total and Grand Total columns). 1139 is the highest value on this row.

Rank - Summarize Values from: [Table (Down)] - Sort Order: [Descending] - Rank Duplicate Values as: [Competition (1,2,2,4)]

Table Calculations for Rank will compare values and produce a Rank for each value. In this case our Sort Order is Descending and Duplicate Values are ranked as Competition (meaning two identical values will get the same rank). 

(1) In the first row, the red box around 4 for Rank was arrived at by comparing all of the values for SUM(Freight) on the Bib-Shorts On Promotion column (excluding the Total and Grand Total rows). 109 is the forth highest value in this column.

(2) In the third row, the red box around 2 for Rank was arrived at by comparing all of the values for SUM(Freight) on the Bib-Shorts Price column (excluding the Total and Grand Total rows). 142 is the second highest value in this column.

(3) In the fifth row, the red box around 1 for Rank was arrived at because it is the only value in the Total row for this Currency Code. 668 is the only and highest value on this row and column (Total and Grand Total rows are not included in the column rankings).

(4) In the seventh row, the red box around 5 for Rank was arrived at by comparing all of the values for SUM(Freight) on the Bib-Shorts On Promotion column (excluding the Total and Grand Total rows). 102 is the fifth highest value in this column.

(5) In the seventh row, the red box around 3 for Rank was arrived at by comparing all of the values for SUM(Freight) on the Bike Racks Price column (excluding the Total and Grand Total rows). 65 is the third highest value in this column.

(6) In the Grand Total row, the red box around 1 for Rank was arrived because it is the only value in the Grand Total row for the Bike Racks On Promotion column. 1139 is the only and highest value on this row and column (Total and Grand Total rows are not included in the column rankings).

Rank - Summarize Values from: [Card Type - Range: Currency Code - Sort Order: [Descending] - Rank Duplicate Values as: [Competition (1,2,2,4)]

Table Calculations for Rank will compare values and produce a Rank for each value across the table. In this case our Sort Order is Descending and Duplicate Values are ranked as Competition (meaning two identical values will get the same rank). 

(1) In the first row, the red box around 4 for Rank was arrived at by comparing all of the values for SUM(Freight) on the Bib-Shorts On Promotion column for the Currency Code AUD. 109 is the forth highest value in this column for AUD.

(2) In the third row, the red box around 2 for Rank was arrived at by comparing all of the values for SUM(Freight) on the Bib-Shorts Price column for the Currency Code AUD (excluding the Total and Grand Total rows). 142 is the second highest value in this column for AUD.

(3) In the fifth row, the red box around 1 for Rank was arrived at because it is the only value in the Total row for this Currency Code. 668 is the only and highest value on this row and column for the Currency Code AUD (Total and Grand Total rows are not included in the column rankings).

(4) In the seventh row, the red box around 1 for Rank was arrived at by comparing all of the values for SUM(Freight) on the Bib-Shorts On Promotion column for the Currency Code CAD (excluding the Total and Grand Total rows). 102 is the highest value in this column for the Currency Code CAD.

(5) In the seventh row, the red box around 1 for Rank was arrived at by comparing all of the values for SUM(Freight) on the Bike Racks Price column for the Currency Code CAD (excluding the Total and Grand Total rows). 65 is the third highest value in this column.

(6) In the Grand Total row, the red box around 1 for Rank was arrived because it is the only value in the Grand Total row for the Bike Racks On Promotion column. 1139 is the only and highest value on this row and column (Total and Grand Total rows are not included in the column rankings).

Percentile - Summarize Values from: [Table (Across)] - Sort Order: [Descending]

Table Calculations for Percentile will compare values and indicate what Percentile of all values this value falls in. In this case our Sort Order is Descending . 

(1) In the first row, the red box around 50% for Percentile was arrived at by comparing all of the values for SUM(Freight) on the Currency Code AUD and Card Type ColonialVoice row (excluding the Total and Grand Total columns). 109 is below 50% of the values on this row.

(2) In the third row, the red box around 75% for Percentile was arrived at by comparing all of the values for SUM(Freight) on the Currency Code AUD and Card Type SuperiorCard row (excluding the Total and Grand Total columns). 142 is below 75% of the values on this row.

(3) In the fifth row, the red box around 50% for Percentile was arrived at by comparing all of the Total values for SUM(Freight) on the Currency Code AUD row. 668 is below 50% of the values on this row.

(4) In the seventh row, the red box around 33.333% for Percentile was arrived at by comparing all of the values for SUM(Freight) on the Currency Code CAD and Card Type Distinguish row (excluding the Total and Grand Total columns). 102 is below 33.333% of all values on this row.

(5) In the seventh row, the red box around 66.667% for Percentile was arrived at by comparing all of the values for SUM(Freight) on the Currency Code CAD and Card Type Distinguish row (excluding the Total and Grand Total columns). 65 is below 66.667% fo all values on this row.

(6) In the Grand Total row, the red box around 25% for Percentile was arrived at by comparing all of the Grand Total row values (excluding the Total and Grand Total columns). 1139 is below 25% of the values on this row.

Percentile - Summarize Values from: [Table (Down)] - Sort Order: [Descending]

Table Calculations for Percentile will compare values and indicate what Percentile of all values this value falls in down the table. In this case our Sort Order is Descending. 

(1) In the first row, the red box around 57.143% for Percentile was arrived at by comparing all of the values for SUM(Freight) in the Bib-Shorts On Promotion column (excluding the Total and Grand Total rows). 109 is below 57.143% of the values in this column.

(2) In the third row, the red box around 25% for Percentile was arrived at by comparing all of the values for SUM(Freight) in the Bib-Shorts Price column (excluding the Total and Grand Total rows). 142 is below 25% of the values in this column.

(3) In the fifth row, the red box around 100% for Percentile was arrived at by summing all of the values for SUM(Freight) in the Bib-Shorts On Promotion column. 668 is the only value on this row and column (Total and Grand Total rows are not included in the column percentiles).

(4) In the seventh row, the red box around 71.429% for Percentile was arrived at by comparing all of the values for SUM(Freight) in the Bib-Shorts On Promotion column (excluding the Total and Grand Total columns). 102 is below 71.429% of all values in this column.

(5) In the seventh row, the red box around 37.5% for Percentile was arrived at by comparing all of the values for SUM(Freight) in the Bike Racks Price column (excluding the Total and Grand Total rows). 65 is below 37.5% of all values on this column.

(6) In the Grand Total row, the red box around 100% for Percentile was arrived at by summing SUM(Freight) values  in the Bike Racks On Promotion column. 1139 is the only value on this row and column (Total and Grand Total rows are not included in the column percentiles).