-
1. Re: Percent of Total not 100%
Julie Stanley Dec 28, 2018 2:14 PM (in response to Julie Stanley)Here's the workbook.
Julie
-
Question.twbx 445.9 KB
-
-
2. Re: Percent of Total not 100%
Rodrigo Calloni Dec 28, 2018 5:16 PM (in response to Julie Stanley) -
3. Re: Percent of Total not 100%
Rodrigo Calloni Dec 28, 2018 5:18 PM (in response to Julie Stanley)Also please check the workbook you added because the viz there isn't the same as the one from the image.
Rodrigo
-
4. Re: Percent of Total not 100%
Julie Stanley Dec 28, 2018 5:44 PM (in response to Rodrigo Calloni)Thanks, good to know!
(edit by Chris McClellan : dodgy looking link removed)
-
5. Re: Percent of Total not 100%
Rodrigo Calloni Dec 28, 2018 6:43 PM (in response to Julie Stanley)If that helped you find a solution please mark the response as completed so others can find the right answer later.
Thanks!
Rodrigo -
6. Re: Percent of Total not 100%
Jonathan DrummeyDec 29, 2018 2:41 PM (in response to Rodrigo Calloni)
1 of 1 people found this helpfulHi Julie & Rodrigo,
In the the attached workbook the % adds up to 100%, however in the screenshot the % adds up to 105.51%. I can see why a rounding error would be suspected, however there are only 11 values and 2 decimal places of precision so being >5 points off seems like too much to me.
The bigger clue for me is that the measure used for the % of total is count distinct of Project ID. Count distinct behaves in non-intuitive ways when used for % of total and that leads to the results shown in the screenshot, and I can explain Tableau's logic here:
In the grand total we can see there are 599 unique project IDs, and this is the denominator used in the % of total. Therefore (starting with the topmost bar) 11/599 = 1.84%, 52/599 = 8.68%, and so on. That explains the percentages, but not why they add up to 105.51%. We have to keep in mind that count distinct, median, and percentile are all "non-additive" aggregations. In other words instead of being able to add up results a, b, and c to get a total the total of a non-additive aggregate is a separate computation at a different level detail.
In this case there are 599 unique project IDs, but since each project can have (appear in) more than one Project complexity the sum of the count distinct of Project ID across the Project complexity's is 612 (I manually added up the displayed values). So instead of 11/612, 52/612, etc. that would add up to 100% because the countd(project id) across all projects is 599 the % of total is being computed with the smaller 599 value and the sum of the % of total adds up to 105.51%.
To help make this clear here's a really tiny data set for an example:
Project complexity Project ID
high 1
high 2
low 1
Here's the results of a COUNTD([Project ID]), the total countd, the % of total:Project Complexity CountD Total countd % of total
high 2 2 2/2 = 100%
low 1 2 1/2 = 50%
Grand Total 2 2 2/2 = 100%
So in this very simple view the % of total adds up to an absurdly large 150%. In some situations this is an accurate number, in other situations the denominator shouldn't be 2 but instead be 3 (i.e. the sum of the count distinct), in that case then the % of total values would be 67% and 33% and would add up to 100%. That might be the case for you, I don't know what you're trying to do here.
Another possibility is that there are unexpected values in the data. In the attached workbook there is 1 and only 1 project complexity value for each project id, and that's why the same measures as in the screenshot add up to 100%. If you're expecting the 1:1 relationship of project complexity:project id to also be true for the original screenshot then you're going to need to look at the data because there's presently a many:1 relationship there.
Hope this helps!
Jonathan
-
7. Re: Percent of Total not 100%
Julie Stanley Dec 29, 2018 4:57 PM (in response to Jonathan Drummey)Thanks, it very well could be the data.