One of the greatest benefits of creating a PowerPivot or tabular model is the ability to easily build custom calculations, using Data Analysis Expressions (DAX). With DAX, you can create custom calculations using mathematical and other functions, inserted in a new column. DAX supports most of the formulas familiar to Excel users (such as SUM, COUNT, AVERAGE), but also enables look ups and filtering across columns and rows to create complicated aggregations as measures.
In this post, I explain some of my favorite DAX expressions in the context of a common scenario – calculating the percentage of an individual value in a data set relative to a total value derived from summing across rows that meet specific filter criteria.
Scenarios for using CALCULATE, SUM, and ALLEXCEPT in DAX
Imagine, for example, trying to calculate the number of sales opportunities closed with a win as a percent of total opportunities for a given sales territory, or summing up sales for a department relative to total store sales for a given time period and region. In these scenarios, we want to be able to duplicate the total value on every row in order to calculate the individual percentage in a new column.
In the example below, there are at least two stores and multiple departments. Let’s assume that the data in your pivot only provides the columns for sales by department (columns Week Ending, Store ID, Region, Dept Name, and Sales in the table below, which I”ll call Department_Sales). How do we calculate the Total Store Sales for each store, so we can then simply divide by Department sales to get the percentage value? We should also be able to add total store sales in order to get the company-wide sales, but we want to only add the distinct values (and eliminate duplicate values for the same store).
How do we do these calculations (Total Store Sales, Dept Sales/Store and Total Corp Sales) within Power Pivot using DAX? I’ll use the data Department_Sales table to demonstrate some quick and easy DAX expressions for calculating Total Store Sales and Total Corp Sales. Once we have the total sales for each store, than it’s simple math to divide that by the department-level sales to get the percentage calculations by department.
To get the total store sales for each store, we need a function similar to SUM IF (e.g., sum the department sales if the Store ID is the same). The functions I’ll use in this scenario are CALCULATE, SUM, and ALLEXCEPT.
Basic DAX Syntax
Every expression in DAX begins with an equal sign, as in Excel formulas. Then you specify the function name, followed by the name of the data table (the tab name in Excel or table in tabular models), and then the name of the column. For example, the basic syntax for SUM is:
The column name must always be enclosed within square brackets [columnname]. In our sample data set, to sum the values in the Sales column, for the Department_Sales table, the DAX syntax is:
If you are just getting started with DAX, Microsoft provides a good DAX overview. Otherwise, let’s build on this basic syntax with more complex expressions.
DAX CALCULATE works like Excel SUMIF and ALLEXCEPT is like T-SQL WHERE Clause
Because we want to specify which rows to sum for each store, I’ll use the CALCULATE expression, along with SUM, because it can take both a mathematical expression as well as filter expressions.
=CALCULATE(expression, [filter1], [filter2], ….[filtern])
In our example, the syntax begins as follows:
To specify the filter( or WHERE clause for those used to T-SQL), I’ll use the ALLEXCEPT function, which means only apply the mathematical expression (in this case, SUM) to the specified column (in this case, Sales) for rows that have the same value as the current row for the columns specified in the filter expression. This is known as “row context.”
The syntax for ALL EXCEPT is:
ALLEXCEPT(tablename, tablename[Columnname1], tablename[Columnname2] …. tablename[columnname n])
So, in our example, I’ll will use the Store ID column in the filter expression to sum the Sales values where other rows in the table have the same Store ID as the current row. In addition, because my table may also contain data for more than one week, I am also going to include Weekending in the filter for ALLEXCEPT. Putting it all together, the final expression in DAX is:
=CALCULATE(SUM(Department_Sales[Sales]),ALLEXCEPT(Department_Sales,Department_Sales[Week Ending],Department_Sales[Store ID]))
In the table, insert this expression in a new column, named Total Store Sales, to get the results as in the example table above.
You now have all the calculations you need to complete the values for Department Sales as a percentage of Total Store Sales. To get the percentage amounts, in another column, create the expression to divide Total Store Sales by the individual Department Sales.
=[Sales] / [Total Store Sales]
Now, how do you enable the pivot to calculate the total sales for all stores without including the duplicate values in the store sales column? I’ll cover that in my next blog in the series, Favorite Quick & Easy DAX, which will explain how to use the SUMX and DISTINCT expressions.