I Want It ALL()

This week I am going to reverse direction on applying filters to my pivot table and show you how and why you might want to remove all filters instead of adding filters to an expression.

Again working with the Contoso dataset, I am going to start by looking at sales by product category. In addition, I want to be able to slice my data by channel or combinations of different channels. To do this, I want to use the visually friendly slicer tool as shown in the following figure. Note that in this case, I already have selected only the Online channel which results in a total of $2.6776 billion in sales. Keep in mind that the total of all sales across all channels is a little more than $12.4 billion.

Now for each product category, I want to see the total sales in that channel compared to the total sales across all product categories and all channels. If I refer back to my earlier blog on the different ways pivot tables can represent data by using built-in features, I might try looking there first to see if there is a fast way to accomplish my task. By right clicking on any of the rows in the Sales Total column and selecting the option: Show Values As, I can pick from a dropdown menu of different built-in calculations. Many of these options calculate percentages of row, column or grand totals of either the entire pivot table or a group level. These options also provide difference and running total calculations.

For example, if I were to select % of Grand Total, I would get some interesting percentages. However, these values would be based on the total sales of the slicer filter, in other words, the total sales for online sales as shown in the following figure.

So let’s play a little with a different pivot table that shows total sales by each of the channels. In the figure below, I’ve included the channel as my row filter and a have two columns which both show the total sales amount. Note that each row of the total sales amount is filtered by the channel. This is an example of filtered context when calculating a measure.

Now I’ve labeled the first of the two column: Total Sales Amount and will therefore let the pivot table display the sum of the measure filtered by the channel.

However, I’ve labeled the second column: % Dales by Channel. I can right click on any of the values in this column to select one of the other built-in calculations. In this case, calculating a percent of the grand total will show me the percent of sales that come from each of the four channels as shown below. The value in the Grand Total row displays 100% because all sales are represented by one of the four channels. Note here that it is clear that online sales account for only 21.57% of the total sales. I can use this information to validate what I’m about to do in the next step.

I am going to create a new measure named: Percent_of_Total_Sales. To generate a value for this measure, I want to sum the column FactSales[SalesAmount] for the filtered context of each place this measure appears in my pivot table. However, to get a percentage of total sales, I need to calculate the sum of FactSales[SalesAmount] for all sales, not just sales for a channel or product category, or any other filter criteria. In effect, I want to calculate the total sales as if there was no filter context in the pivot table at all. I can do this by creating a ‘new’ table for the SUMX() function (remember SUMX() has two parameters, the first of which must reference a table of values). There is another function I must use to eliminate the filter context for this ‘new’ table. This function is aptly named: ALL(). When I use ALL(FactSales) (and yes, the parameter for the ALL function must be the name of the table and it returns a table with all filters removed), I can get a ‘copy’ of the FactSales table without applying the filter context of the pivot table. In other words, all of the records in the original FactSales table will be included in ALL(FactSales). If I use this ‘new’ table as the data source for my SUMX() function and then simply sum the Sales Amount column using the SUM() function as shown below, I can return the total sales of the unfiltered FactSales table which then can be used as my denominator in my calculation. The numerator is a SUM() function of the Sales Amount also, but is calculated on the filtered context which in my case is filtered for online sales and product category.

Initially the measure returns a value of 1 because in the data model there is no filter context so the sum of the ‘filtered’ sales amount total divided by the ‘unfiltered’ sales amount total will be equal to 1. Rather than go directly to the pivot table, let’s first format this value as a percent by right clicking on the measure definition cell and selecting Format.

This option displays a dialog that lets me select the data category which is: Number. I then select the format of the number as Percentage with 2 decimal places. I then click OK to accept the format for the measure.

My measure calculation in the data model now displays a value of 100.00%. However, if I place this measure in my pivot table that displays sales by product category and uses a slicer to include on online sales, I can see my sales percentages as shown below. These values are now correctly dividing the product category sales for online sales by the total sales of my company. I can feel confident that the calculation is correct because the percent in the Grand Total line which represents sales from all product categories made through online sales is 21.57% which is the same percent I calculated in the pivot table earlier that only looked at sales by channel.

Using the channel slicer, I can select store sales instead of online sales. My previous pivot table told me that this should be 55.93% of the total sales. As you can see in the following figure, the Grand Total of my pivot table that displays sales by product category has percentages that also (accounting for rounding) add up to 55.93% since this pivot table uses the channel slicer.

In case any reader is wondering, the second pivot table that displays sales by channel, while on the same spreadsheet as the first table that displays sales by category does not use the slicer. If you have a slicer and multiple pivot tables or pivot charts, you must associate the slicer with each table and/or chart to which you want it to apply. It is not automatic nor implemented by spreadsheet page.

I hope you are starting to get a better feel for how row context and filter context work together with DAX expressions and functions to calculate values. Being able to correctly apply and remove filter contexts is essential in calculating values in many pivot table tables.

C’ya next time.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s