DAX Calculations and Queries - SQL Server Analysis Services 2012 Cube Development - page 386

Using the CALCULATE function
The purpose of the CALCULATE function is to apply a measure or calculation expres-
sion to a filtered set of rows, usually by calling another function that returns a table ob-
ject. 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 SalesTerrit-
oryCountry 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] =
"United States" )
2. Move on to another cell and write a similar measure named Non-US Sales .
The only difference from the previous example is that the value is not equal to
United States . It should look similar to the following code:
Non-US Sales:=CALCULATE( [Sales Amount],
'Internet Sales', 'Sales
Territory'[SalesTerritoryCountry] <>
"United States" )
Test the Measures in Excel
1. Switch back over to Excel, and on the Data ribbon, click on Refresh All .
With the PivotTable selected, you should see the US Sales and Non-US
Sales measures added to the Internet Sales fact table.