DAX: CALCULATE  function in action

We will be using data contained in a excel table in an excel spreadsheet.

The table reflects the quantity and the sales amount in a specific month of different products sold in different regions and cities. It also indicates if the sale was a regular sale or a promotional sale

The table is called 'Calculate'

The data in the table is added to the Data Model 

In a separate post we have explained how to add data to the Data Model

Once the data is in the Data Model we can create Measures as explained here!

The measures that we create are

1) sum('Calculate'[Sales])

This measure is called SalesAll. It sums all the numbers in the 'Sales' column of the 'Calculate' table. 

2) CALCULATE(sum('Calculate'[Sales]),'Calculate'[Sales Type]="Promotional")

The measure is called Sales_Promotional. It sums the amounts reported in the 'Sales' column of the 'Calculate' table where the corresponding value in the 'Sales Type' column is equal to 'Promotional'

3) [Sales_Promotional]/[SalesAll]

The third measure is based on the two previously created It divides the Sales_Promotional by the SalesAll in order to calculate what % of the total sales are promotional. 

The measure is called Sales_Perc_Promotional

DAX Measures have this very useful feature: they can be referred to and create new measures. 

The measures that we have created show up as available fields to build a Pivot Table from the Data Model 

We have explained here how to build la Pivot Table using the data in the Data Model

A Pivot Table is created by dragging the cities filed in the rows section and the two measures SalesAll as first and Sales_Perc_Promotional as second  in the values field

The resulting Pivot Table shows the sum of sales in each city and what percentage of those sales were Promotional

In  the rows section of the above pivot table we have inserted the 'City' column.

The SalesAll measure is then inserted in the values section of the Pivot Table. 

It is important to note that as all cities have a values in the corresponding 'Sales' column in the table  

If one of the cities had no values in the 'Sales' column it would have not been reported. This is because the logic of the DAX Measure rules over the logic of the Pivot Table 

This is what the Pivot Table looks like if the city of Madrid has no values in the 'Sales' column.

Let's now look to the second measure Sales_Perc_Promotional

The measure displays the percentage of promotional sales only for the cities which have this type of sales - i.e. the ?Sales Type' column shows the value 'Promotional'

The Sales_Perc_Promotional is built on top of other measures. If we would be using it as the only measure in the values field of the Pivot Table it would be displaying only cities for which a percentage has been calculated. 

This post has explained how to use DAX measures and especially the Calculate function in order to create columns in a Pivot Table that provide details of numbers in different columns

This allows to provide additional  information to the Pivot Table without adding additional fields that would make it more difficult for the reader to understand the data