Finding The Percent Of Total In Power BI

by | Power BI

In this article, we will review how to find the percent of the total calculation in Power BI.

Although you might have already learned this from the other modules, reviewing it would be beneficial for its common usage in various scenarios.

Percent Of Total In Total Sales

To show you a simple example, we will create a measure for Total Sales.

To create this measure, I will use the SUM function and then put in the Total Revenue column.

Creating Total Sales measure - Percent of Total Power BI

Then, I will drag Total Sales into the canvas and make an association with the Product Name dimension.

Associating Total Sales with Product Name - Percent of Total Power BI

Then, we need to figure out the percent of sales of all the products under the Product Name column through the total.

Product Name and Total - Percent of Total Power BI

To do this, we need to divide every single number in Total Sales by the total. The only way to achieve this is to change the context of the calculation so that the Product Name column would be ignored.

We will create a new measure called Every Sales, reference the Total Sales inside CALCULATE, and then use the ALL function with the Product Name column since it can remove filters from the dimension.

Creating the Every Sales measure - Percent of Total Power BI

After dragging in the measure, you can see that every single row has the same result.

Showing values of Every Sales - Percent of Total Power BI

This is because in this entry, the context which is Product 7 is already removed by the ALL function, making every single row show the same result.

Product 7 example - Percent of Total Power BI

To get the percent of total, we will create a new measure called % of Total which uses the DIVIDE function to divide Total Sales by Every Sale, and then put in a zero as the optional alternate result.

Creating % of Total measure - Percent of Total Power BI

We can now drag in our new measure and change the format to show percentages.

Showing % of Total measure - Percent of Total Power BI

These two measures would not be necessary so we can remove them.

Removing two unnecessary measures

After removing those two intermediary calculations, this is how the table would finally appear.

% of Total with Product Name

To better visualize the percent of total table, we can select the stacked bar chart under Visualizations and then sort the results into a descending order.

Creating stacked bar chart of the percent of total table
Stacked bar chart after sorting

Getting the percent of the total was very simple since all we had to do is to put in the correct dimensions then use the ALL function to remove the filters for that calculation.

Percent Of Total Power BI: Context Considerations

In this section, we will discuss the context considerations surrounding percent of total in Power BI.

A lot of the results that you get from percent of total calculations heavily depend on the context where you place your formula.

For example, the context in this particular example is Product 7.

Example of percent of total entry

All of these percent of total results add up to 100% since we have removed the filters for Product Name inside the formula of Every Sale.

Because of this, we are able to do that intermediary calculation with DIVIDE wherein the sum of the total was used as the denominator.

However, the percent of total formula can return a different result depending on which context you put it into.

For example, I will copy and paste the table and then use Customer Names as the context for the calculation.

Using the Customer Names column

As a result, the percent of total in every row displays 100%.

% of Total with Customer Names

This is because the % of Total measure does not work in this context since we need to remove the filters first.

However, if we put in the other dimensions, the Every Sales measure would not work since only the filter in Product Name is being removed in our formula.

To show you an example, the Every Sales measure here is still returning the values from the Total Sales of the customers.

Every Sales returning Total Sales

If we want to get the percent of total per customer, we need to make changes in the Every Sales measure or change the table using a slicer.

Slicer button

By selecting a customer through a slicer, the table of results will now work since the percent of the total is now being filtered by a particular customer.

Using slicer to filter customers

Whenever you click a customer name within the slicer, you are looking at a reduced Sales table instead of looking at the entire Sales table.

Another example for slicer filtering

Instead of selecting one customer at a time, you can also change the slicer settings so that selecting multiple customers would also be possible.

Slicer settings

Selecting multiple customers will still yield correct results since the Product Name context is properly used.

Using slicer to select multiple customers

Simply changing the context or using slicers would enable you to efficiently retrieve desired results in various situations.

***** Related Links *****
Calculating Dynamic Percentage Of Total Change Using Power BI Time Intelligence
Power BI Percent Of Total – Using CALCULATE Statement
Calculating Percent Profit Margins Using DAX In Power BI

Conclusion

That is how you get the percent of total in Power BI and how using different contexts affect your calculations.

Understanding how context works is very important since you can get drastically different results by changing it as shown in our examples.

These are just the basics when dealing with percent of total calculations. Combining what you have learned with other concepts would eventually allow you to do more advanced calculations.

All the best,

Sam

Related Posts