Creating Window Calculations
The Dashboard Designer allows you to add a window calculation for numeric measures. To do this, invoke the data item menu and select the required calculation type.
The image above shows a calculation menu of the Pivot dashboard item. The following items are available.
Percent of Column Grand Total - Calculates a contribution of individual measure values to a column grand total.
Percent of Row Grand Total - Calculates a contribution of individual measure values to a row grand total.
Percent of Grand Total - Calculates a contribution of individual measure values to a grand total.
Running Summary along Columns - Calculates a cumulative total for measure values along columns (horizontally).
Running Summary along Rows - Calculates a cumulative total for measure values along rows (vertically).
Difference along Columns - Calculates differences between measure values along columns (horizontally).
Difference along Rows - Calculates differences between measure values along rows (vertically).
Percent Difference along Columns - Calculates percentage differences between measure values along columns (horizontally).
Percent Difference along Rows - Calculates percentage differences between measure values along rows (vertically).
Rank along Columns - Ranks measure values along columns (horizontally).
Rank along Rows - Ranks measure values along rows (vertically).
Rank along Cells - Ranks measure values along cells (throughout the entire pivot table).
Custom... - Allows you to create a custom calculation by specifying various settings. Clicking this item invokes the Customize Calculation dialog that allows you to add additional customizations to calculations.
To learn more, see descriptions of the available calculations below.
Running Total
The Running Total calculation can be used to compute a cumulative total for the specified measure across a window. For example, the Grid below displays cumulative sales across all quarters.
The Customize Calculation dialog provides the following settings for the Running Total calculation.
functions, see the Summary Function Types in the summary function topic.
Moving Calculation
The Moving calculation uses neighboring values to calculate a total. For example, the Grid below shows a moving average across all quarters.
The Customize Calculation dialog provides the following settings for the Moving calculation.
Summary function - Specifies a summary function used to apply a calculation. To learn more about the available summary functions, see the Summary Function Types in the summary function topic.
Start offset / End offset - Specify start/end offsets from the currently processed value. For instance, if you specified offsets as 1/1, the previous and next values will be used along with the current value to apply the Moving calculation.
Difference
The Difference calculation can be used to compute the difference between measure values across a window. For example, the Grid below shows absolute differences between quarterly sales.
The Customize Calculation dialog provides the following settings for the Difference calculation.
First and Last.
You can also use the Percent Difference option to specify whether the absolute or percentage difference is displayed.
Percent of Total
A calculation is used to compute a percentage of the total for the specified measure across a window. For example, the Grid below shows a contribution of individual quarterly sales to total sales.
The Customize Calculation dialog provides the following settings for the Percent of Total calculation.
Rank
Use the Rank calculation to compute rankings for the specified measure across a window. For example, the Grid below shows a ranking of sales for individual quarters.
The Customize Calculation dialog provides the following settings for the Rank calculation.
Percentile.
Order - Specifies the order of ranking. You can select Ascending or Descending.
Expression
Use Expression to specify a custom calculation by adding the required calculation functions inside the measure expression.
Click the Edit in Expression Editor button to invoke the Expression Editor and specify the required expression.
The Expression type provides the Calculate along option that specifies the window and direction used to calculate differences. Note that this option is in effect if the expression contains a calculation function.