This blog post is Human-Centered Content: Written by humans for humans.

This blog could well begin with a familiar dilemma for any data analyst, with two split messages: a) a Superman-like figure armed with what I’ve learned and thought I’ll need in my daily work b) a somewhat disappointed knowledge worker finding out what I’ll be doing repeatedly. But don’t despair, the repeated calculations are modest, but they’re the ones we’ll probably need the most to draw conclusions from the data quickly and efficiently. One of them is the good old percentage of the total.

I’m going to show you how to create percentages in not one but two tools, Tableau and Power BI, that you can add to your broad analyst skill set. How’s that for a challenge? Let’s dive right in.

Basic Percent of Total Calculation — Tableau Approach

1. Quick Table Calculation

In Tableau, the humble Percent of Total has found its way into the Hall of Shortcuts, or Quick Table Calculations, so you can have it at your fingertips whenever you need it. And below, you can see the simplest way to create this calculation — just pay attention to the structure of your table and the size and direction or how your calculation moves across the sheet.

If you look behind the automatic calculation, you’ll see this formula:

SUM([Quantity]) / TOTAL(SUM([Quantity]))

2. Level of Detail (LOD) Expressions

The same behaviour as above can be achieved with a level-of-detail calculation. In this case, to get the division by total, we’ll need to calculate at a higher level and exclude the more granular ship mode (alternatively, you could achieve the same by using the FIXED expression — just be careful about the order of operations and which filters you want to apply):

SUM([Quantity]) / SUM({ EXCLUDE [Ship Mode]: SUM([Quantity])})

Basic Percent of Total Calculation – Power BI Approach

As with Tableau, Power BI allows you to calculate the same parts-to-total ratio in different ways, and using the powerful DAX (Data Analysis Expressions) language to create dynamic, context-aware calculations.

An easy way to calculate the percentage of the grand total in Power BI is to click on the measure, in our case Total Profit, and select the shortcut Show Value as: “Percent of Grand Total.” For example, to calculate the percentage of total profit across all of our subcategories, we would then have this table view:

But let’s say you want to make this view a bit more dynamic and add a slicer to the subcategory so you can filter by clicking on one of the options. That’s not a result we would expect:

Advanced Percent of Total Calculations

For more complex scenarios, Power BI allows the creation of measures that can dynamically calculate the percent of total across different dimensions and filters.

One way is by using Measures and the CALCULATE Function. This method involves creating measures for total values (e.g., total sales or profit) and then using the CALCULATE function to adjust the context of the calculation — in our case, with the ALL function (another option would be to use REMOVEFILTERS function) to ignore filters applied to Subcategory column. Like so:

Percent of Total Profit = DIVIDE([TotalProfit],
CALCULATE([TotalProfit], ALL(DIM_SUBCATEGORY[Sub-Category])))

This formula calculates the profit percentage by dividing the total profit by the total profit across all products, regardless of any filters applied to the Subcategory dimension:

Something good to know about context in Power BI: Understanding the context in which your calculation is being performed is key to accurate percentages in Power BI. Context can have a significant impact on the result, especially for more complex visualisations. As we saw in the example above, using slicers or changing dimensions can change the Percentage of Total calculations because they change the underlying data context. This flexibility allows for dynamic analysis that adapts to user interactions and data selections, but will also leave you wondering what happened to good old simple Percentage of Total.

How Do They Compare?

Both Tableau and Power BI offer robust ways to calculate percentage of total, each with its own set of features and approaches tailored to the platform’s strengths. Tableau’s focus on direct, visual data manipulation contrasts with Power BI’s DAX formula approach, which offers deep customisation and context-aware calculations. The choice between them often depends on the specific needs of your analysis, the complexity of your data and your preference for visual vs. formula-based data manipulation. Whichever tool you choose, you’ll be well-equipped to solve the common problem of every data analyst. Check out our other resources on both Tableau and Power BI to learn more!