Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Calculations In Power BI Using Measure Branching

by | 7:00 pm EDT | October 11, 2020 | Power BI

Measure Branching is a technique in making calculations in Power BI. It’s not something you’ll hear often because it is unique to Enterprise DNA.

It’s a methodology that I made to create measures out of measures. You may watch the full video of this tutorial at the bottom of this blog.

## Calculating The Total Costs Using The RELATED Function

I’m going to use the example of creating a calculation on profits. To compute our profits we need to subtract our sales from our costs. Now, if you look at our table of raw data in the Sales table, we can’t do it because the data is not in our fact table.

But what we can do is utilize information which is in another table to compute for our costs.

In the Products table, we have the cost information.

Using this data, we can calculate what our cost is in the Sales table and subtract costs from revenues to get our profit.

The great thing is that we can do this all within measures.

First, I’m going to create a new measure called Total Costs. And I’m going to use SUMX because we need its iterating function to do this. Next, I’m going to reference Sales and Quantity and multiply it by the cost.

Now, the cost column doesn’t exist in our Sales table. So, we’re going to use a function called RELATED.

This function returns a related value from another table. This is an interesting function which is not used often but this is a perfect example of where you would use it because we need to retrieve a value from another table.

Next, I’m going to input the Product Cost column from the Products table. Then close off the brackets and enter the formula.

In this formula, the RELATED function grabs the associated cost for each individual product at every single row from the Products table. This is possible because of the relationship between each table.

You’ll see now that we have the Total Costs per customer.

Once we’re done, let’s remove the intermediary calculations since they are unnecessary.

We can also format the data here. To do that, click on the Measure Tools Tab. For example, I can format the currency into a dollar sign to help with the data interpretation.

## Calculating The Total Profits Through Measure Branching

We now have two measures: the Sales measure and Costs measure. These are the measures we need for calculating our Total Profits.

Next, I’m going to create another measure called Total Profits. The formula for this is the difference between Total Sales and Total Costs.

When typing in the measure, the IntelliSense identifies the measure immediately and we can select it from the drop-down.

Now I have a new measure which is being created out of my core measures. This is the start of measure branching.

When we put this measure into the table, we can see what the Total Profits are per customer.

The great thing here is that we don’t need intermediary calculations. The measure branching is going to work behind the scenes.

To use this in a visualization, I can input the new measure inside a visualization and filter it any way I like. For example, I’m filtering it by customers, but I could also filter it by county.

This is the visualization of Total Profits by Customer:

This is the visualization of Total Profits per County:

These are all calculated using measures.

## Calculating Profit Margin Using Measure Branching

We can make this even more advanced by creating a new measure called Profit Margin. I’m going to use a function called DIVIDE, input Total Profits, and divide it by Total Sales with the alternative result of zero.

This Total Profits measure was created using our core measures and we’re reusing it again to create the Profit Margin calculation.

Now we have Profit Margins and we could turn this into a visualization as well.

## Conclusion

This is what measure branching is. It’s reusing measures within measures.

It starts with making one measure then as you go on with more advanced calculations, you use the primary measures to create new measures.

You start off with simple calculations then begin to advance into complex calculations with no effort.

Throughout the process, I have not created any additional data within my tables. I don’t need to create any calculated columns at all. All the calculations you need are within measures.

When you take things to the next level by calculating for your cumulative total, cumulative revenue, cumulative profits, and comparing them between dates, you can reuse these measures over and over again.

All the best,

Sam

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## How to Interpolate in Excel: User Guide With Examples

In data analysis, interpolation plays a crucial role in estimating values that fall between known data...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

## Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....

## Power BI Certification: Everything You Need to Know

In today's data-driven world, the ability to transform raw numbers into meaningful insights is more...

## Power BI Bookmarks: The Ultimate Guide

When working with data, bookmarks offer a streamlined and personalized way to navigate through large...