How to use the ALL function in DAX for your Pivot Tables

We will be using data of an 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!

We will create a measure that will allow to sum all the values in the 'Sales' column where column 'Sales Type' reports 'Promotional'

The measure is called Sales_Promotional and is the following

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

We build a pivot table inserting the City item in the rows and the Sales_Promotional measure in the values field

Despite the 'Calculate' table reflects nine unique City names the pivot table reports only six. 

The reason is that the Sales_Promotional measure overrides the Pivot Table logic of the City in the rows and displays only the Cities that have Promotional in the 'Sales Type' column

How can I display all the cities on the rows and calculate how each of them contributes to the total promotional sales?

Let's take it in steps

1) We create a measure to display all the Cities by using the CALCULATE function together with the ALL function

In the Expression part of the Measure we report the Sales_Promotional measure and in the part of the filters we use the ALL function applied to the 'City' column

Since the ALL function forces the display of all cities, even those without promotional sales, the value shown corresponding to each City is the total sales.

This measure is called Sales_Promotional_All

We now have a measure that sums the promotional sales in each City (Sales_Promotional) and a second measure that for each City reports the gran total of promotional sales (Sales_Promotional_All)

In order to get to the final result we create a third measure called Sales_Promotional_Percentage_All

=[Sales_Promotional]/[Sales_Promotional_All]

The measure is displayed in the Pivot Table together with the Sales_Promotional_All

The column with the measure Sales_Promotional_Percentage_All shiws the percentage of promotional sales doen in each city compared to the total of promotional sales