# Advanced DAX Functions: Calculating Profit Contribution

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.

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.

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

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

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

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

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.

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.

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.

## 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.

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.

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.

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.

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

Then I did the same for Total Profits.

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.

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.

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

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.

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.

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.

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

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.

## 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.

Drag the measure into your table and fix the format.

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.

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

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

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.

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.

## 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

## MAXX in Power BI – A Detailed Guide

A hands-on guide to implementing data analysis projects using DAX, focused on the MAXX function and its combinations with other essential DAX functions.

## Leveraging the COUNTX Function In Power BI

Learn how to leverage the COUNTX function in DAX for in-depth data analysis. This guide provides step-by-step instructions and practical examples.

## Using the FILTER Function in DAX – A Detailed Guide With Examples

A comprehensive guide to understanding and implementing the FILTER function in DAX, complete with examples and combinations with other functions.

## DATESINPERIOD Function in DAX – A Detailed Guide

Learn how to implement and utilize DAX functions effectively, with a focus on the DATESINPERIOD function.

## Using the DISTINCT Function Effectively in DAX

A systematic exploration of the DAX DISTINCT function to optimize data analytics.

## Guide and Many Examples – ALL Function in DAX

A detailed guide to understanding, implementing, and mastering the DAX ALL function, complemented by practical examples and combinatory techniques.

## Detailed Guide to SWITCH function in DAX

A comprehensive guide to mastering DAX functions in Power BI for conducting advanced data analysis.

## SUMMARIZE Function in DAX – A Deep Dive

A comprehensive guide to using the DAX function SUMMARIZE in Power BI, with detailed explanations and practical examples.

## Your Data Visualization Doesnâ€™t Look Great. What Should You Do?

Data visualization is the key to unlocking the insights hidden within your data. But, what if your...

## Leveraging Power BI for Data-Driven Decisions

In the world of data analytics, thereâ€™s a constant demand for tools that not only help you make sense...

## Understanding Data Models and Visualizations

Power BI is a robust and versatile data visualization tool that has gained popularity for its...

## Getting Started with DAX in Power BI: A Beginnerâ€™s Guide

Data analysis expressions (DAX) are the key to unlocking the superpowers of Power BI. If you want to...