If you work with Tableau on the day-to-day basis, you might need to show a percentage of your dimensions. It's a tricky question. When you try to exclude some rows with categories, the calculation will be wrong! Tableau distributes 100% by existing categories by default.
In this article, I will explain how to calculate the percentage ignoring the filters.
What on earth should you do to allocate the percentage with numbers exclusion? Apparently, you should aggregate, right? But how can we compute an aggregation? There are a lot of different formulas in Tableau. Actually, the Fixed LOD helps you to leave the values constant after exclusion.
In Tableau, as in any data visualization program, you adapt it’s own syntax. If you want to use the program, you need to learn the syntax be heart (just kidding). The Level of Detail (LOD) expression is considered as one of the most complex functions, but no worries, it’s can be created in just some steps.
Now, let’s get it started!
Step 1: Add a new connection in your Tableau Data Source panel. I use Retail Sales by Product table.
Step 2: Create a table on the sheet: drag Category into Rows and Sales into Text.
Step 3: Convert Sales into per cent of Total. Click on SUM(Sales) — Quick Table Calculation — PercentofTotal.
So, the table has been created.
If you don’t have a grand total, you can demonstrate this row by navigation to the Analysis tab in the menu bar and selecting Totals — Show grand totals.
Well.. this table looks a bit unorganized now. What can you to polish it?
Step 4: Sort categories by descending. You can also remove the decimals to make it more structured: right click on any number in Sales column — Fields — % of Total sum — Number — Percentage — choose Decimal places 0 instead of 2. Looks better?
The initial process of Fixed LOD calculation includes two steps
- create of Total LOD,
- calculate Percent of Total LOD.
Step 5.1: Create calculated field ‘Total LOD’. Navigate to the Analysis tab (or just right click on the drop-down-menu on the right side from search form) — choose create calculated field. Name the calc ‘Total LOD’ and enter the formula
{FIXED : SUM([Sales ($1,000)])} . Click Apply, click ok.
Make sure, that you include the dimensions relevant to your table. You can drag the name of Sales value from the menu. Drag ‘Total LOD’ to the table column with % of Total Sales and you can see the following results.
The calculation shows a number 8,439,643. That’s our total 100% Sales.
Step 5.2: Create per cent of Total LOD using the same logic. The function is
SUM ([Sales ($1,000)])/SUM([TOTAL LOD])
Drag ‘Per cent of Total LOD’ to the table column with % of Total Sales.
An important moment is to convert number into percentage format.
Step 6: Click on the number — format — Fields — AGG(Percent of total LOD from drop-down) — number — percentage — decimal places 0.
Here we go! The same percentage as in the first column.
Now, if we select ‘Exclude’, for example, for Automotive fuels category, our results have not been affected or changed by removal. You obtain the right results in the second column.
Make sense? Yes? No?
Advance with Assist shares quick solutions to common challenges encountered by the InterWorks Assist on-demand team. Question: My calculation for percentage isn’t returning the right values, but the data is there. I’m getting the
sum of a single product, not the full sum. What do I need to do?
Understanding what Tableau does with calculations and aggregations is important when building calculated fields. All of us have found ourselves in similar situations as this user. Now, in this example there are many ways to approach the question. I will show three ways to get the desired results she was after:
- Quick Table Calculation
- Calculated Field
- LOD
Quick Table Calculation
This is the simplest option and the one that I would try first. Table calculations are common functions built into Tableau for you to use. One of those options is Percent of Total. These calculations also allow you to compute the calculations depending on how you’ve set up your view.
The Quick Table Calculation appears when you right-click a measure pill in your Tableau user interface:
Showing compute:
The user now had the percentages she was after.
Calculated Field
Quick calculations can be made into fields by dragging the pill with the delta symbol to the data window. By doing this, you can see the calculation that Tableau is using to compute the percentage:
LOD
One last note for this question is just an expansion of the concept above. This would be using LOD calculations. In the above example, there is a filter on Product Name to only keep those five products, but there are far more products in my sales data. Table calculations only use the data you’ve narrowed for calculation. Maybe a secondary question here is: what is the percentage across all products instead of just these five? LOD may be a good approach.
For this, we will have two logical steps: how many products sold across the company, and the number of product sold by each name.
So the calculation would look like this:
Replace fields in this example with your data to see how this can be applied. Hope you find this useful!