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!
