Advanced DAX Functions: Calculating Profit Contribution

No comments

In this blog, I’ll be discussing some more advanced DAX functions to demonstrate what’s possible in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

This tutorial will teach you how to visualize the profit contribution of different client tiers using advanced DAX functions.

Calculating Total Profits

I’m going to start by creating a new measure and renaming it as Total Costs.

Creating a new measure for total costs

To get the cost, you need to use the SUMX function, which returns the sum of an expression evaluated for each row in a table.

Add the Sales table and multiply each sales quantity to the corresponding product cost.

The SUMX function applied to the Sales table

You will need to use the RELATED function to call on another table, particularly the Products Cost table, to achieve this.

The RELATED function retrieving values from the Cost column

Hit enter and wait for the calculation to appear on your Key Measures.

The total costs measure appearing on key measures

From here, you can branch out easily to Total Profits. Create a new measure and subtract Total Costs from Total Sales.

The formula for total profits using measure branching

As you may have noticed, you don’t have to reference anything in the table anymore.

This is the beauty of measure branching: you can reference measures within measures rather than having to rewrite advanced formulas repeatedly.

This methodology is a far more effective way to do complex calculations and it will save you a lot of time.

Now I’m going to drag the measures for Total Costs and Total Profits into my table.

Dragging the total costs and total profits measures into the table

I also like to fix the format right away just so my data is organized.

Select ‘Currency’ in the format drop down menu or click the currency symbol.

Formatting the columns to make values appear as currency

You now have Total Sales, Total Costs, and Total Profits. The next step is to identify who in this time selection are the top clients, the mid-ranged customers, and the rest.

Measures to be used in doing advanced DAX functions in Power BI

Creating A Supporting Table

After setting up my basic measures, I’m going to create a supporting table.

This will enable me to create dynamic groupings based on custom logic.

Click ‘Enter data’ to add a new table.

Click enter data to create a supporting table for advanced dax functions

Name the supporting table “Customer Groups” and add columns ‘Group’, ‘Min’, and ‘Max’.

Afterward, group the clients if they belong to the Top 50, Rank 50-200, and The Rest. Indicate the minimum and maximum ranks of each group as shown below.

The customer groups table to be used for advanced dax functions

Whenever you create a new table in Power BI, it’s best to check and organize your data model so you have an overview of how everything is connected.

In this example, I deleted irrelevant measures and dragged my supporting table to the bottom.

As you can see, it has no relationship with my core data model. The goal is to run logic through this table based on the ranking of my customers. This step will require advanced DAX functions in Power BI.

Using Advanced DAX Functions

To start, I’m going to sort my supporting table.

Go to the column tools of the Customer Groups table and sort the Groups column according to the minimum ranking. I like to do this because the data will make more sense later on.

Sorting the supporting table to display neater data

Before proceeding, I’m going to make some visualizations. I went ahead and transformed my Total Sales into a card.

Visualizing total sales as a card

Then I did the same for Total Profits.

Visualizing total profits as a card

With all that set up, I’m going to bring in my Total Profits measure inside the Customer Groups table.

Since there is no relationship between the two, you’ll see that the Total Profits of $5.01 million goes across every single customer group.

Total profits is not properly segmented without advanced DAX functions

To fix this, I’m going to create a new measure and write an advanced formula. While it is complicated, using advanced DAX functions in Power BI will make this achievable.

I’m going to show you the entire formula for Customer Group Profits first and then I’ll explain what each line does.

The advanced dax function used to run logic through the supporting table

Running the advanced DAX function and inserting the measure into your table will give you the Total Profits of each customer group.

Customer Group Profits calculated using advanced dax functions

So how did this happen?

The formula generated context to relate the supporting table to Total Profits.

The CALCULATE function was used to call on Total Profits but in the context of a modified filter.

The CALCULATE function used in the advanced dax function

Using VALUES, the advanced DAX function iterated profits per customer by calling on each Customer Name.

Moreover, FILTER enables it to add a table function. In this case, it’s bringing up my list of customers.

The FILTER function sorts customers based on their rank

Data was filtered further using RANKX. This function worked out the ranking of every customer based on Total Profits.

It also determined if a customer’s ranking is greater than the minimum ranking of each customer group or less than or equal to the maximum ranks. This way, each entry fell only within one group without any overlap.

In short, RANKX was responsible for sorting customers into their appropriate Customer Groups.

The FILTER function sorts

Meanwhile, COUNTROWS specified that if there is one more row remaining in the table, the customer should be included in that particular group.

COUNTROWS counts table rows to be included in Customer Groups

In the end, you’re able to go through every customer and evaluate if they fall within the minimum and maximum ranks of a particular row. This will place them either in the Top 50, Rank 50-200, or The Rest.

These advanced DAX functions make it possible for the Customer Group Profits column to show the total profits of a specific customer group.

The customer group profits column is made possible by advanced DAX functions

Calculating the Percentage Contribution Of Each Bracket

The analysis can be taken further by calculating the percentage of profits per customer group.

For this, you’ll need too create a new measure called Customer Profit Group Percent.

Use DIVIDE to get the percentage of the Customer Group Profits by the Total Profits. The Total Profits always evaluates to $5M in this context.

Calculating the Customer Profit Group Percentage using the DIVIDE function

Drag the measure into your table and fix the format.

Dragging the Customer Profit Group Percent into the table

Now you have a column for the Customer Profit Group Percent. As you can see, its values are what you get when you divide the profit of a specific customer group by the Total Profits.

The advanced DAX functions paved the way to compute the percentage contribution of each bracket

You can actually remove intermediary columns like Total Profits and the advanced DAX function will run the calculation just fine behind the scenes.

Removing unnecessary columns will not affect advanced dax functions

What’s great about this is you can add additional context then make visualizations of the result. Just copy the table and then drag fields into Values.

Here, I added Month & Year and turned it into a percentage chart.

Adding context and visualizing customer group profit percent

I swapped around which fields go into the axis and legend. Month & Year needs to be on the Axis while Groups should be placed on Legend.

Switching fields to make the bar graph more meaningful

What I have now is a bar graph that shows which customer group gives the most profit per month and how the trend changes over time.

A bar graph showing which customer group brings in the most profit per month

Conclusion

Advanced DAX functions are valuable tools in Power BI. They are versatile and can give you rich insights for your reports. Hopefully this tutorial has given you an idea of how to use them in your own context.

In the next blog posts, I’ll discuss what else you can do with advanced DAX functions. Until then, try exploring this example on your own.

All the best!

Sam

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

Membership Banne

***** Related Links*****
Manage Multiple Date Calculations In Your Fact Table – Advanced Power BI Technique
Optimizing Your Power BI Formula Using Advanced DAX Functions
Data Segmentation Techniques Based On Any Measure – Advanced DAX

***** Related Course Modules*****
Ultimate Beginners Guide to DAX
Advanced DAX Combinations
Mastering DAX Calculations

***** Related Support Forum Posts*****
Create Custom Dynamic Groups In Power BI – Advanced DAX
Problem with Ranking (RankX), Showing all Customers
Grouping Customers Evenly By Rank
For more advanced DAX calculations to review see here…..

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.