The DAX language is deceptively complex. The dynamics of the language itself, are actually easy. It's the nuances that trip people up, even experienced DAX programmers. If you are just getting started, though, you'll likely struggle on where to start. This article will help you with many projects. We'll discuss the four core DAX measure that you should always create from the start.
Many DAX projects work with sales data. Most of the tutorials or articles you search for on Power BI or DAX will use sales data as the samples to illustrate the techniques. This is likely due to sales data is what many business owners are most concerned with. After all, if they can't increase sales, they won't stay in business for very long.
Related: How to Get Started in Power BI
Because a fair number of projects will be sales-based, it makes sense to define measures that will be used for these types of projects. When I first started learning DAX, I never knew where to start on a project. With this short tutorial, you'll know exactly what measures to produce as a starting point.
By defining a set of core measures that should be included for every project, they will become second nature to you. That is something I wish tutorials taught me in the beginning. The core four DAX measures are Total Sales, Total Cost, Total Profit, Profit Margin.
When you define a set of core measures, you'll have at your disposal the tools you need to define more complicated measures. That's because most of the Key Performance Indicators will be derived from these core measures.
Total Sales
The Total Sales measure is an aggregation of the table that contains your sales information. This is usually called Sales or Orders. Some datasets will call this the Transaction table. No matter what it's called the table will usually contain the quantity of products sold and the price. Unfortunately, sometimes the price exists in other tables, like the Products table. This complicates defining the Total Sales measure, but only slightly. We'll cover this below.
If you are lucky, you'll have a column that is already defined called Sales Amount or Revenue. This is by far the easiest when creating measures, but the least likely. Let's start with the easiest definition: when a Sales or Revenue column is already defined in the Sales table.
Here is how to define the Total Sales measure:
Total Sales := SUM('Sales'[Revenue])
If the column in the Sales table is called Sales Amount, then Total Sales would be defined as:
Total Sales := SUM('Sales'[Sales Amount])
See how simple that is?
If you need to calculate the total sales, then you'll need to use SUMX() instead of SUM(). Suppose we have the Quantity Sold and the Price columns, both defined in the Sales table:
Total Sales = SUMX(Sales, 'Sales'[Quantity Sold] * 'Sales'[Price])
What if one of the columns needed for the calculation exists in another table? Let's suppose that Price is not in the Sales table, but instead in the Products table. In this case, you'll use the RELATED() function, as follows:
Total Sales = SUMX(Sales, 'Sales'[Quantity Sold] * RELATED('Products'[Price])
Total Cost
The same logic applies to Total Cost. The difference is that instead of using price, you'll use the cost column. You'll need to locate where your costs reside. You'll see it in both the Sales table and the Products table.
This discovery is all part of the Exploratory Data Analysis (EDA) process. This occurs at the beginning of a data analysis project and is used to find out where to source the data that will be used to answer the questions defined by the initial requirements.
Let's assume that as part of your EDA process, you discover that the cost is contained in the Sales table. Therefore, to get the total cost, you would multiple the Cost x Quantity Sold:
Total Cost := SUMX('Sales'[Quantity Sold] * 'Sales'[Cost])
You may see Cost referred to as Unit Cost in many datasets.
Total Profits
Okay, here is where it gets really hard (sarcasm included!)
Since you already have Total Sales and Total Cost, Total Profits is simply Total Sales - Total Cost:
Total Profits := [Total Sales] - [Total Cost]
Yep! That is how simple it is to get the Total Profits measure!
Profit Margin
Profit Margin := DIVIDE([Total Profits], [Total Sales], 0)
You have noticed for the profit margin that the DIVIDE() function is being used. It's not required to use this function, but you'll be happy you did. If you tried to divide the two numbers using the standard divide symbol (/) you'll run into errors when the denominator is 0. The DIVIDE() function in Power BI helps avoid that situation from ever occurring.
Related: Need Data for Your Projects? Take this course to learn how to find data for your projects.
These Four Core Measures Serve as Building Blocks
After you define these four core measures, you create a foundation for future measures to be developed. For instance, if you need to get average sales per some category, you'll use the [Total Sales] measure as the base for that calculation. The same is true for the other measures, too.
Why Not Create Calculated Columns Instead?
Most beginners to Power BI and DAX default to using calculated columns. That's usually because they are familiar with working in spreadsheets. The reasoning makes sense as all of the data is contained in the tables and all you need to do is use the other columns in the table to define the new calculated column. It's much more intuitive for beginners to go this route. But with BI tools it will come back to haunt you in the long run.
Why?
Because calculated columns take up memory and tools like Power BI are meant to handle large amounts of data. The more memory you consume by defining calculated columns, the faster you'll run out of memory. You'll also end up slowing down your model, too!
Measure don't consume memory until they are used, and most measures are used for aggregations. Aggregations use up significantly less memory. The downside is that measures are a more difficult concept for people to grasp. That's because it's hard to get your head around what the underlying components of the aggregation are and what filters are applied to the measure for it to create the results.
Conclusion
The topics of the basic of DAX and filters are too vast to cover in one article and would bloat the article unnecessarily. There are plenty of tutorials on the topic and I will be including some on this website soon. The takeaway for this article is to learn the basics of DAX and then define the core functions that you'll likely use in many projects going forward. This will remove the guesswork from the process and will set you to a point in your learning where you'll be further ahead than most beginners.
