4 Replies Latest reply on Jan 14, 2020 10:24 AM by Ken Flerlage

    Table Calculation Exceeding 100%

    SOU SOU Phomsavanh



      I'm trying to do a simple table calculation that shows the workload types based on days so if we add up all the workload types it should add to 100% for the day. As you can tell in the screen shot why for some days are exceeding 100% and others are below. I am just using a quick table calculation > percent of total > compute using cell. I could write an LOD but thought this would be quicker. Any help would be greatly appreciated.



      Version 2019.2.0

        • 1. Re: Table Calculation Exceeding 100%
          Ken Flerlage

          I think the problem here is that your calculated field is a COUNTD. The math may not always work out the way you want it to, in this case. For example, if one of the Workload values exists in multiple Edit Kind 2's, then it will count that twice--once for each Edit Kind 2 (this is the numerator). But then Tableau will do an overall COUNTD for the entire date. In that case, it will get only 1 (this will be the denominator). It then performs the math and results in values that are > 100%.


          For this reason, an LOD would probably have the same result.


          Does that make sense?

          • 2. Re: Table Calculation Exceeding 100%
            Simon Runc

            hi Sou Sou,


            So this is due to you having the same workload in multiple Edit Kind 2



            The default formula for % of Total is

            [workload count]/total([workload count])


            Total computes the countd of workload irrelevant of the Edit Kind 2 dimension, so for example workload NVTRQ167 it gets counted in both Volatility and Volatility Lock in the [workload count] but only once in the total.


            We can get round this by writing our own % of total calculation like so

            [workload count]/window_sum([workload count])


            by using window_sum we are going to sum up the count for each Kind Edit 2 so in the above example NVTRQ167  gets counted twice on both sides.


            You'll also need to amend the compute using like so....


            Hope that makes sense?

            • 3. Re: Table Calculation Exceeding 100%
              SOU SOU Phomsavanh

              I didnt realize part of the issue was due to the multiple workload kind. Thanks for taking the time!

              • 4. Re: Table Calculation Exceeding 100%
                Ken Flerlage

                Just be careful to make sure that's what you want. Calculating percentages using distinct counts can get you into so dangerous places statistically speaking.

                1 of 1 people found this helpful