2 Replies Latest reply on Feb 16, 2017 1:31 PM by Stephen Shepperd

# Calculating percent of total when SUM is different from total - Tableau 10.1

I have a database with only partial data that I am allowed to view.  I can see 4 of the 6 groups and know the total, but do not know what the 2 groups I cannot view contribute individually on a lower level.

Below is what I can do in Excel by using an absolute reference to the total sales of 298 but cannot translate it to Tableau:

 Group Sales Percent of Total A 25 8% B 39 13% C 45 15% D 89 30% E unknown 34% F unknown Total 298 100%

I have tried using EXCLUDE as well as ATTR but end up with errors on combining accumulated data.

This is similar to what my database looks like:

 Group Item Number Sales A A234 18 A A235 7 B A236 20 B A237 10 B A238 9 C A239 20 C A240 25 D A241 46 D A242 9 D A243 12 D A244 22 All groups all items 298
• ###### 1. Re: Calculating percent of total when SUM is different from total - Tableau 10.1

Hi Erik,

Here is one idea.

[Group 2]

if contains([Group],"ALL") then "Total" else "Individual" end

[Percent Contribution]

SUM([Sales])/attr({fixed:sum(if [Group 2]="Total" then [Sales] end)})

Overview: Level of Detail Expressions

Thanks,

Shin

• ###### 2. Re: Calculating percent of total when SUM is different from total - Tableau 10.1

Hey Erik,

Here's a simple idea. What you can do is write a calculation that takes the sales of each group and divides it by the WINDOW_MAX which is always going to be the "All groups" value. It will look like this:

SUM([Sales])/WINDOW_MAX(SUM([Sales]))

Then you can just change the number format to show in percentages and you should be done!

Hope this helps!