In my last post, I explained how to create a Calculated Column that aggregates values in the Power Pivot data set for rows that meet specific filter criteria (by using the CALCULATE, SUMX, and ALLEXCEPT functions, you can customize exactly which rows you want to have included in your sums for a column). In this post, I explain how to do a distinct sum on that column, where you specify the values that you want to have included in the totals. We’ll look at how to use a DISTINCT so that you don’t double count duplicated data.
Scenarios for using SUMX and DISTINCT expressions in DAX
In the retail store scenario in my last post, I added up the sales values for a store each day, in order to be able to create a measure of the percent each department contributed to store sales. Now I want to use the column holding store sales to get total company-wide sales for all stores. However, notice that in this column, we have duplicate values because each row of data is at the department level and therefore store sale totals show up multiple times for a given day.
Here is what the data set looks like – the challenge is to write a measure in DAX that will generate the total month to date sales for all stores without including duplicates for the same store and day. Let’s call this table in Power Pivot Department_Sales table.
Create a Measure to Calculate an Average Value
In the column with duplicated values, use the AVERAGE function, which we will leverage in another measure in order to get a distinct value by store and day. We’ll also want to eliminate any possible zero values in our average, so we’ll also apply a filter for that. Whenever you want to specify values to exclude in a function as a filter, use CALCULATE(<functionX>(FILTER as in the expression below:
AverageValue:=CALCULATE(AVERAGEX(FILTER(<tablename>, <expression>),<column with values to average>))
In our example, the syntax is:
AverageValue:=CALCULATE(AVERAGEX(FILTER(Department_Sales, [Total Store Sales]<>0),[Total Store Sales]))
Enter the expression in the measure area below the column:
Create a Measure to Sum the Distinct Average Values
The use of AVERAGE enables us to have a way to identify a single value for each Weekending + Store ID combination (e.g., the average of the same value 4 times is the distinct value). In order to make this work in DAX, we need to create a custom key that joins the Weekending value with the Store ID value. For this, we’ll just use a new calculated column, and enter the following expression, and call is WeekStoreKey.
=[Week Ending] & [Store ID]
Now we can create the final expression to sum only the distinct values for each Week Ending and StoreID. In order to use the DISTINCT expression, you embed it within a SUMX:
The Power of Custom Aggregations with DAX
With this scenario, you can see how easily DAX lets you customize the values in a column that are included in an aggregation function such as a sum. In my next blog in the series, Favorite Quick & Easy DAX, I’ll explain how to perform look-ups on related tables in order to perform further calculations as well as how to exclude rows using multiple criteria for filters.