DAX: Revenue, Cost and Profit

After having set up a basic data model and date table, now it is time to start analyzing the data by writing DAX measures. I will start by creating a new table to store the measures and then write the DAX formulas for Total Sales, Total Cost, Total Profits and Profit Margin.

1: Creating a table to store the DAX measures

After opening the DK Retailer .pbix file, I navigate to the Enter data button on the ribbon. This will open the Create Table window. I leave the column and row empty and under Name I type “Main Measures” and click on Load.

The table will appear in the Fields pane on the right of the canvas. It has an empty column and row.

2: Calculating Total Sales

Now it’s time for our first DAX measure. Let’s start by calculating Total sales/Revenue. In order to do that we need to first look at the Sales data and find out whether we have the necessary information to calculate Revenue. I go to the Data pane and select the Sales table from Fields.

As it can be seen above, the Sales table contains information for each transaction. Each transaction refers to one product and the corresponding quantity sold. However, there is no Sales Price in the Sales table. The Sales Price is saved in the Products table as can be seen below.

One of the main caveats of Power BI and DAX is that all the measures refer to columns and rows and not individual cells as is the case with Excel. Even though the columns needed are in separate tables, through the power of the data model we can relate these columns by writing the appropriate DAX measures.

To calculate Total Sales, we need to use a so-called iterating function. It is called an iterating function because it evaluates each row in a table. I go ahead and click on the New measure button in the Calculations group on the ribbon.

There I type in the following DAX measure:

This measure takes the Quantity column in the Sales table and then it multiplies with the Current Price column in the related table Products. It goes through each row in the two tables and returns the aggregation we need, in this case a multiplication and then sums it all up at the end.

3 Calculating Total Costs

The process for calculating Total Costs is similar to the previous step. Again, I click on the New measure button and type in the following measure:

4 Calculating Profit and Profit Margin

To calculate Profit we need to subtract the costs from the revenue and with DAX measures it is very easy to do so by referring to the already created measures. Again, I click on New measure and type in the following Total Sales followed by the minus sign and Total Costs. Notice how the color changes to purple.

5 Formatting the measures

Before calculating the Profit Margin I want to show another way of writing a measure. First of all, we need to make sure that the three measures are in the Main Measures Table. In the Fields pane I can see that I have the three measures.

However, I want this table to be placed on top of the other tables to differentiate it. To do that, I first need to delete the Column 1 inside it. I right-click on Column 1 and click on Delete from model.

By doing so, the Main Measures table has now been sent to top. The icon also changed automatically to one with a calculator confirming that this is a table containing measures.

Now it’s time to format the measures and choose how many decimal places the numbers should have. I click on the measure and then under Measure tools, I choose Currency and two decimal places for all three measures.

6 Write DAX from Table

Another way of writing a DAX measure is by right-clicking in one of the tables from the Fields pane. This means that the new measures will be automatically stored in that table. So, I go ahead and create the measure for Profit Margins. Here I use DIVIDE and add a 0 for Alternate Result. At the end I format this to a percentage with two decimal places.

7 Create a Table

Lastly, let’s put all these measures inside a table. From the Visualizations pane, I click on Table. In the Values I drag the measures one by one. The resulting table shows the Revenue, Costs, Profits and Profit Margins for all the data.

DAX Measures used in this video:

Total Sales	SUMX( Sales, Sales[Quantity] * RELATED( Products[Current Price] ) )
Total Costs	SUMX( Sales, Sales[Quantity] * RELATED( ‘Product'[Cost] ) )
Total Profits	[Total Sales] – [Total Costs]
Profit Margins	DIVIDE( [Total Profits], [Total Sales], 0 )