7 Replies Latest reply on Dec 29, 2018 4:57 PM by Julie Stanley

    Percent of Total not 100%

    Julie Stanley

      This is a Percent of Total with a calculation using Table (down). As you can see, while I expect this and see that this should add to 100%, it is not. Is this a rounding error or something else?

       

       

        • 1. Re: Percent of Total not 100%
          Julie Stanley

          Here's the workbook.

           

          Julie

          • 2. Re: Percent of Total not 100%
            Rodrigo Calloni

            Hi Julie

             

            Thanks for the workbook. I believe you are right, this is about rounding the %s

             

            I've exported the %s to Excel and ran the sum there just to double check and it does show 100%

             

            Screen Shot 2018-12-28 at 8.14.42 PM.png

             

            I hope this helps

            Rodrigo

            • 3. Re: Percent of Total not 100%
              Rodrigo Calloni

              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

                Thanks, good to know!

                 

                (edit by Chris McClellan : dodgy looking link removed)

                • 5. Re: Percent of Total not 100%
                  Rodrigo Calloni

                  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 Drummey

                    Hi 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

                    1 of 1 people found this helpful
                    • 7. Re: Percent of Total not 100%
                      Julie Stanley

                      Thanks, it very well could be the data.