12 Calculations for Calculating Part of the Pie/Percent of Total in Tableau
In this blog, I will show you 12 calculations for calculating the Percent of the Total or Part of the Pie in Tableau.
We will be using Sample Superstore for the exercise.
- Simple Quick Table Calculation (TOTAL() Function)
Drag Sales to Text. Drag Region to Rows. Change Mark Type to a Text. Right-click on Sales -> Quick Table Calculation -> Percent of Total. Then Sort the same in descending. If you see the calculation, it would be as shown below.
SUM([SALES])/TOTAL(SUM([Sales]))
The snapshot of the output is shown below.
2. Simple Table Calculation (WINDOW_SUM() Function)
We can also use WINDOW_SUM() for calculating Percent of Total. Create a Calculation as shown below.
SUM([SALES])/WINDOW_SUM(SUM([Sales]))
Drag POT Window Sum Calculation to Text. Drag Region to Rows. Change Mark Type to a Text. Sort the same in descending. The snapshot of the output is shown below.
3. Non-Aggregate with Fixed LOD
We can also calculate Percent of the Total with the formula shown below. It combines Fixed LOD and a non-aggregate field.
[Sales]/{SUM([Sales])}
{SUM([Sales])} means FIXED which fixes on nothing so total Sales would be displayed for the same.
Drag Non Agg with Fixed LOD Calculation to Text. Drag Region to Rows. Change Mark Type to a Text. Sort the same in descending. Change the Format of the calculation to be of Percentage. The snapshot of the output is shown below.
4. Fixed LOD with Fixed LOD
We can also calculate Percent of the Total with the formula shown below. It combines 2 Fixed LODs, one that fixes on nothing and the other that fixes on Region.
{ FIXED [Region]: SUM([Sales])}/{FIXED : SUM([Sales])}
Drag Fixed FIxed POT Calculation to Text. Drag Region to Rows. Change Mark Type to a Text. Sort the same in descending. Change the Format of the calculation to be of Percentage. The snapshot of the output is shown below.
5. Fixed with Table Calculation (TOTAL() Function)
We can also calculate Percent of the Total with the formula shown below. It combines Fixed LOD and a Table Calculation TOTAL().
SUM({ FIXED [Region]: SUM([Sales])})/TOTAL(SUM([Sales]))
Drag Fixed with Total Calculation to Text. Drag Region to Rows. Change Mark Type to a Text. Sort the same in descending. The snapshot of the output is shown below.
6. Fixed with Table Calculation (WINDOW_SUM() Function)
We can also calculate Percent of the Total with the formula shown below. It combines Fixed LOD and a Table Calculation WINDOW_SUM().
SUM({ FIXED [Region]: SUM([Sales])})/WINDOW_SUM(SUM([Sales]))
Drag Fixed with Window Sum Calculation to Text. Drag Region to Rows. Change Mark Type to a Text. Sort the same in descending. The snapshot of the output is shown below.
7. Include LOD with Fixed LOD.
We can also calculate Percent of the Total with the formula shown below. It combines a Fixed LOD and an Include LOD. We will include the Region and fix the Sales on nothing.
{ INCLUDE [Region]: SUM([Sales])}/{FIXED : SUM([Sales])}
I can also use {SUM([Sales])} instead of {FIXED : SUM([Sales])}
Drag Include POT Calculation to Text. Drag Region to Rows. Change Mark Type to a Text. Sort the same in descending. Change the Format of the calculation to be of Percentage. The snapshot of the output is shown below.
8. Include with Table Calculation (TOTAL() Function)
We can also calculate Percent of the Total with the formula shown below. It combines Include LOD and a Table Calculation TOTAL().
SUM({ INCLUDE [Region]: SUM([Sales])})/TOTAL(SUM([Sales]))
Drag Include with Total Calculation to Text. Drag Region to Rows. Change Mark Type to a Text. Sort the same in descending. The snapshot of the output is shown below.
9. Include with Table Calculation (WINDOW_SUM() Function)
We can also calculate Percent of the Total with the formula shown below. It combines Include LOD and a Table Calculation WINDOW_SUM().
SUM({ INCLUDE [Region]: SUM([Sales])})/WINDOW_SUM(SUM([Sales]))
Drag Include with WindowSum Calculation to Text. Drag Region to Rows. Change Mark Type to a Text. Sort the same in descending. The snapshot of the output is shown below.
10. Exclude LOD with Fixed LOD
We can also calculate Percent of the Total with the formula shown below. It combines a Fixed LOD and an Exclude LOD. We will exclude the Region and fix the Sales on nothing.
SUM({EXCLUDE: SUM([Sales])})/sum({FIXED : SUM([Sales])})
I can also use {SUM([Sales])} instead of {FIXED: SUM([Sales])}
Drag Exclude with Fixed Calculation to Text. Drag Region to Rows. Change Mark Type to a Text. Sort the same in descending. The snapshot of the output is shown below
11. Exclude with Table Calculation (TOTAL() Function)
We can also calculate Percent of the Total with the formula shown below. It combines Exclude LOD and a Table Calculation TOTAL().
SUM({ EXCLUDE [Region]: SUM([Sales])})/TOTAL(SUM([Sales]))
Drag POT Exclude with Total Calculation to Text. Drag Region to Rows. Change Mark Type to a Text. Sort the same in descending. The snapshot of the output is shown below.
12. Exclude with Table Calculation (WINDOW_SUM() Function)
We can also calculate Percent of the Total with the formula shown below. It combines Exclude LOD and a Table Calculation WINDOW_SUM().
SUM({ EXCLUDE [Region]: SUM([Sales])})/WINDOW_SUM(SUM([Sales]))
Drag POT Exclude with WindowSum to Text. Drag Region to Rows. Change Mark Type to a Text. Sort the same in descending. The snapshot of the output is shown below.
Thanks for reading
Vignesh Suresh
