7 Replies Latest reply on May 16, 2018 11:32 AM by Okechukwu Ossai

    Calculated %'s add up to 99%, 100% and 101%

    conacher

      Hello,

       

      I have a 10.5 Packaged Workbook. that places two sheets in a dashboard.  The percentages in the right-hand "Total Injuries" don't sometimes add up to 99%, 100% (perfect!) and 101%.  My users do not want decimal places displayed so it appears to them that the calculations are incorrect (everything should add up to 100%, right?).

       

      I've read how Tableau calculates %'s but I don't see how I can achieve what I need, which is to have the %'s in the across the bottom and down the right column add up to 100%.

       

      Here are examples of the incorrect %:

      • Birmingham's Total Injuries (right column) %'s add up to 101%
      • Villa Casa - same thing
      • Empire Education - Bottom row adds up to 99%
      • Intelligentsia - Total Injuries (right column) %'s add up to 101%, Bottom row adds up to 99%
      • Wine County - Total Injuries (right column) %'s add up to 101%, Bottom row adds up to 101%
      • Central Valley - Total Injuries (right column) %'s add up to 98%, Bottom row adds up to 99%

       

      And yet some calculate correctly:

      • California Security Co.
      • Doolittle & Associates
      • Mountain Education
      • Christian Brothers Education
      • Southwest California Education
      • St. Francis Religious Studies
      • Texas Academics
      • Washington Ed. Assistance

       

      It's, I believe, a fairly simply report.

       

      Any fixes available?


      Thanks for your help.

       

      ~S

        • 1. Re: Calculated %'s add up to 99%, 100% and 101%
          Okechukwu Ossai

          Your calculations are correct. The extra or deficit percentage is due to round up error. If you change the number format to percentage, 2 decimal places then you will see that the numbers all add up to 100%.

           

          For example, Central Valley adds up correctly,

          1.12 + 2,25 + 22.47 + 12.36 + 2.25 + 39.33 + 3.37 + 16.85 = 100%

           

           

          Hope this helps.

          Ossai

          1 of 1 people found this helpful
          • 2. Re: Calculated %'s add up to 99%, 100% and 101%
            conacher

            It does help.  One more question.  I need the parentheses around the percentage which it doesn't appear I can configure in the Percentage option (but can with the Custom option).  Any trick to using the Percentage option and include () around the resulting number? 

             

            Thanks so much.

            • 3. Re: Calculated %'s add up to 99%, 100% and 101%
              Okechukwu Ossai

              Yes, you can add the desired number of decimal places including the brackets in the Custom option. For example, 2 decimal places enclosed in brackets will be (0.00%). See image below.

               

              Hope this helps.

              Ossai

               

              • 4. Re: Calculated %'s add up to 99%, 100% and 101%
                conacher

                Additionally, is there a way I can only list the %s in Total Injuries Totals fields (end of tan lines on right side)?  Not the total counts in the partition.

                 

                Thank you!

                • 5. Re: Calculated %'s add up to 99%, 100% and 101%
                  Okechukwu Ossai

                  Do you mean listing the individual Cause percentages in the Grand totals - Districts' worksheet?

                  • 6. Re: Calculated %'s add up to 99%, 100% and 101%
                    conacher

                    No, like in the Central Valley example, is it possible to simply list the % and not the total items and the %?  See example attached.

                     

                    Thank you!

                    • 7. Re: Calculated %'s add up to 99%, 100% and 101%
                      Okechukwu Ossai

                      Yes and No.

                       

                      It's very difficult because that column is the Row Total Column. You have 2 measures on the Text marks (count and %). You can either completely exclude a measure from the Total in all columns and rows. However, there is no functionality to exclude a measure from the rows total in selective columns and rows of the view. Another option is to add percentage as an independent column but that won't work since it will alter the current structure of your table. If you want to completely remove Count from the Total, then right click on the measure, go to Total using (Automatic) and Select 'Hide'.

                       

                      However, it seems you want to retain the totals in other rows and columns but just exclude it from the last column. The only workaround I can think of is to use a calculated field to hide the Count values. However, this means the Column Grand Total for Counts (15) won't display either. This may be a compromise if this is the format you want.

                       

                      Create calculated field [Count of Records]

                      IF ATTR([Department]) = "Teacher - Special Education" THEN SUM([Number of Records])

                      ELSE

                          IF LAST() <> 0 THEN SUM([Number of Records]) END

                      END

                      This is a table calculation. Set it to compute using Table (across). Remember that this formula will work strictly with your current setup. "Teacher - Special Education" must be the last department in the table. If it changes then you'll need to update the formula.

                       

                      Hope this helps. Please mark my answer as helpful or correct if it resolves your question.

                      Ossai

                       

                      1 of 1 people found this helpful