SQL Server Analysis Services 2012 Cube Development Cookbook

Using the CALCULATE function

The purpose of the CALCULATE function is to apply a measure or calculation expression to a filtered set of rows, usually by calling another function that returns a table object. We will use the FILTER function to explicitly filter rows in the Internet Sales table where related rows from the Sales Territory table have a SalesTerritoryCountry column value equal to United States. Using these functions, we will define the filter context.

How to do it…

Follow the given steps to use the CALCULATE function:

1. Return to the Internet Sales table in grid view and select a new cell in the Calculation Pane. Write a new calculated measure named US Sales as follows:

   US Sales:=CALCULATE( [Sales Amount], 'Internet Sales', 'Sales Territory'[SalesTerritoryCountry] ...