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

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

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
• Washington Ed. Assistance

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

Any fixes available?

~S

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

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%

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%

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%

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%

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%

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%

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.