7 Replies Latest reply on Oct 11, 2019 4:24 AM by Michael Hesser

    How to calculate percentage of a specific range over the total?

    James Lai

      Hi all,

       

      I m looking to calculate the percentage of count in the histogram under the lower limit in the histogram.

       

      I have a parameter called {Iccmeas pl Istdby RV]

       

      The parameter is rounded off to 3 decimal places via a calculated field called "[Nearest 3]".

       

      The parameter is then compared to a value called "lower limit" and "upper limit" found in the secondary data source.

       

      I need to use an IF statement to find the total count below the "lower limit" to compute the percentage.

       

      Something like:

       

      IF AVG([Nearest 3])< ATTR([lower limit]) THEN ......

       

      However, I am still unable to compute the ideal calculated field.

       

      Any inputs will be appreciated.

      My apologies I am unable to attach the package workbook as my file size exceeds the permissible size range.

       

      James

        • 1. Re: How to calculate percentage of a specific range over the total?
          Michael Hesser

          Hi James;

           

          There could be a lot of different factors here-- are you able to attach a reduced-size sample TWBX so we can understand the basics (data, joins, table details) ?

           

          Your initial equation looks okay-- what errors are you getting when you try to complete it? Is Tableau saying you can't combine those data types in an expression? Are the resultant values incorrect?

           

          I'd like to see the expression where you are generating the color... you should be able to use a similar equation to count the number of records within those bins less than (or greater than) your target amounts.

           

          Dividing these numbers by the total number of records should yield a percent of total, which I think is what you're after. Let me know if this is not what you want!

           

          Maybe you could also help me with the phrase "I am still unable to compute the idea calculated field"-- are you just saying you can't get the equation right, or that you are expecting Tableau to do a little number crunching and perhaps return a number to you? (I've done this before using a percential calculation where I want Tableau to give me the optimal range or results where at least 90% of files pass).

           

          I realize this is a little confusing, but if you could get us a boiled-down sample (it can be super simple!), that will help a great deal!

           

          Thanks, and Happy Vizzing!

          • 2. Re: How to calculate percentage of a specific range over the total?
            James Lai

            Hi Michael,

             

            Thank you so much for your reply!

             

            I have just updated my post and attached my twbx file.

             

            The calculated field I have created for the color on my histogram is "Limit with duo data source".

             

            Yes indeed. What I am after is the yield percentage you have mentioned.

             

            So far, I can only understand that i have to make use of this IF statement, IF AVG([Nearest 3])< ATTR([lower limit]) THEN ...... but the big problem is I do not know how to total up the number of records within this boundary since there is no countif function available in Tableau.

             

            Appreciate your kind assistance and inputs.

             

            Best regards,

             

            James

            • 3. Re: How to calculate percentage of a specific range over the total?
              Michael Hesser

              Hi James;

              Let me tell you what I've discovered. Spoiler: no resolution ...but what I've unearthed may help others solve your problem.

               

              I started using the KISS process (Keep It Simple, Stupid). In other words, I tried to reduce the issue to the most basic stage and build up from there. It looks like you know your way around Tableau, so this method may be fairly simplistic to you, nevertheless, here's the stages I went through

               

              Displaying individual [Nearest 3] members-- I just dropped [Nearest 3] on Columns and converted it to a dimension

              Display count of [Nearest 3]-- I dropped [Number of Records] onto Rows and converted my view to BARS. This matched up real well with your Histogram.

              Shifted count of instances to % of instances. Rather than using your CNT(Nearest 3) with a quick calc, I used SUM(Number of Records) with the Percent of Total quick calc. This was personal preference.

              Determined if I could calculate percent of records under a specific amount (I'm leaving out the connection to your second table right now). I could-- it worked!

               

              Count Under

              COUNT(IIF(([Nearest 3])<.255,1,NULL))

              //counts the number of elements < .255

               

              Count Under Total Pct

              {SUM([Count Under])}/{count([Number of Records])}

              //Calculates what percent of all records those meeting the [Count Under] criteria represent

               

              Using this formula (and this basic set-up), 7.55% of your records fell under the limit of .255

              Determined if replacing the model with [Nearest 3 (bin)] broke it-- it didn't! I just replaced [Nearest 3] on Columns with [Nearest 3 (bin)].

               

              Determined if I could replace the static ".255" with [Lower Limit] from your second table. Here's where things when bonkers: I couldn't do it-- Tableau wouldn't allo wme because of linking issues I'm not sure how your two files are connected, but it wouldn't let me aggregate the numbers like I wanted. Perhaps someone else can figure this out?

              • 4. Re: How to calculate percentage of a specific range over the total?
                James Lai

                Hi Michael,

                 

                Greatly appreciate your inputs and taking your time to try resolve this problem!:)

                 

                I tried using your equation and replacing it with the parameter "lower limiit" as shown below.

                 

                COUNT(IIF(AVG([Nearest 3])<ATTR([Sheet1 (Limit data test)].[Lower Limiit]),1,NULL))

                 

                //counts the number of elements < .lower limit

                 

                Sadly, it didnt work:( I inserted an ATTR function make it an all aggregate value but doing this does not enable the COUNT function to work since, it has already been aggregated.

                 

                The "lower limiit" is a single value as compared to Nearest 3 which consists of many rows of values, not inserting the aggregation will not enable the IF function to work.

                 

                Ill keep trying and see if there is any other alternatives.

                 

                Anyway thanks for your help:)

                 

                Best regards,

                 

                James

                • 5. Re: How to calculate percentage of a specific range over the total?
                  Michael Hesser

                  James--

                  I believe your BLEND is the problem: you're not telling Tableau how to slice & dice the tables together, and including those fields within the view, so Tableau can't tell which Upper and Lower Limits to apply to the data to calculate them and you're getting unsupported aggregation errors.

                   

                  Here's how I was able to solve it:

                   

                  I started with an Underdog Flag:

                   

                  Underdog Flag

                  IF AVG([Nearest 3])<=ATTR([Sheet1 (Limit data test)].[Lower Limiit]) THEN 1 ELSE 0 END

                   

                  I put [Underdog Flag] in Detail, but I don't think this is necessary.

                   

                  Now I leveraged some WINDOW CALCS:

                  I don't think I've ever fully matured because I still like breaking things down into little tiny pieces; so I made this guy whose sole purpose is to count all the records on the screen:

                   

                  Window Count All Records

                  WINDOW_SUM(count([Nearest 3]))

                   

                  There were 11,808 records, which appears correct once we've eliminated all the NULLS. I consider this a temporary calculation because I'm using it for a gut-check to make sure the values are in line with expectations.

                   

                  Last but not least, count how many are Underdogs, and divide by total number of records to get a percent:

                   

                  Window Count of  Underdogs

                  WINDOW_SUM(iif([ Underdog Flag]=1,count([Nearest 3]),NULL) ) /

                  WINDOW_SUM(count([Nearest 3]))

                  //We could substitute the last line with [Window Count Records] if we wanted to.

                   

                   

                  Hope this helps?

                  • 6. Re: How to calculate percentage of a specific range over the total?
                    James Lai

                    Hi Michael,

                     

                    WOW sir, you are fantastic! It works!

                     

                    I thought it was impossible.

                     

                    Thank you so much for your help!

                     

                    Best regards,

                     

                    James

                    • 7. Re: How to calculate percentage of a specific range over the total?
                      Michael Hesser

                      It's no problem at all

                      There are likely more elegant ways to do this-- I'm just pleased we found one solution!

                      Happy Vizzing! --Michael