First, let's look at the TOTAL() function. The TOTAL() function applies the underlying aggregation to the entire pane instead of each individual row.
- Create a text table with two hierarchical dimensions on the rows
|Sales by Sub-Category|
- Calculate the total sales across all categories
- Calculate the total sales for each category
- Create the following calculated fields
Take care to notice what values are being summarized over.
- Add these values to the table
|Sales by Sub-Category (with Totals)|
This example is somewhat silly because this is much better accomplished by adding Row Subtotals. However, this is a great example to show how the TOTAL() function works.
Now, let's move on to Window Calculations. We will use WINDOW_SUM(); however, the same techniques can be applied for the other Window Calculations as well. A Window Calculation requires three input parameters. These parameters are an aggregated value, a displacement value for the starting point, and a displacement value for the ending point. For example,
WINDOW_SUM( SUM( [Sales] ), 0, 0 )
will calculate the SUM of SUM of [Sales] for the current row only, which is just the SUM of [Sales]; whereas
WINDOW_SUM( SUM( [Sales] ), FIRST(), LAST() ) or just WINDOW_SUM( SUM( [Sales] ) )
will calculate the SUM of SUM of [Sales] for the entire pane, which is identical to using
TOTAL( SUM( [Sales] ) ).
- Calculate the sum of each Sub-Category along with the previous and next Sub-Category
- Create the following calculated field
|Sum of Neighbors|
- Add this value to the table
|Sales by Sub-Category (with Sum of Neighbors)|
Now, you can see that each value is the Sum of [Sales] for this Sub-Category as well as the two neighboring Sub-Categories. Care should be taken to avoid situations such as the first as last row, where there is no previous or next value, respectively.
There is so much more to be done here. Try combining Window Calculations with different aggregrations, such as Sums of Averages, or try creating your own Percent of Total calculations. I hope you found this informative. Thanks for reading.