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

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

12

123

41

3

6

9

3

197

Your total is 197.

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

12

6.1%

123

62.4%

41

21%

3

1.5%

6

3.0%

9

4.5%

3

1.5%

197

100%

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:

12

0

123

111

41

(-82)

3

(-38)

6

3

9

3

3

0

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

1. Right click on a Measure or Continuous Data item in the Rows or Columns deck and select [Add Table Calculation...]

2. Within the [Add Table Calculation] Dialog select a [Calculation Type] (Difference From, % From, % Difference From, Running Total, % Of Total or Moving Calculation)

3. Choose how you wish to define your calculation with [Summarize the values from]: 

4. Choose a range from [Range] and click OK

5. The Table Calculations will replace the data within your Table. You can see what Table Calculation settings are in place by mouse hovering over a cell and looking at the tooltip.

When appropriate, a new row or column will appear within the Fields Labels explaining the Table Calculation.

Any fields in the Rows or Columns decks which have Table Calculations will display a special icon to their right to make identifying them easier.

 

% 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 which 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 which 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 which 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 which 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.