7 Replies Latest reply on Apr 20, 2018 11:39 AM by Mark Holtz

    Percentage not calculating correctly

    Char Miller

      Hello -

       

      I want to add a percentage to my view and I can't seem to get it to work. My set up is:

       

      Create a calculated field (Net)

      Create a second calculated field (collection percentage) as follows ((Payed + InsPayed) / Net) and express this as a percentage

       

       

      Here's how it's laid out in my view so far. Only one percentage is calculating correctly:

       

      Here is my calculated field. I'm aggregating by average:

       

      I looked at the data behind this point:

       

      I can see rows where the calculation isn't right, but I can't figure out what's going wrong:

       

       

      I've attached the spreadsheet shown above as Excel if anyone wants to use it to upload to a workbook. Sorry, I'm not able to upload the entire workbook for security reasons.

       

      If anyone can help me figure out what's happening here, I'd greatly appreciate it.

       

      Cheers,

      Char

        • 1. Re: Percentage not calculating correctly
          ShivaRam Chennapragada

          Could you modify your Collection Percentage to this and see if it works,

           

          FLOAT(SUM([Payed]) + SUM([Ins Payed]))/SUM([Net])

          1 of 1 people found this helpful
          • 2. Re: Percentage not calculating correctly
            Mark Holtz

            I may be missing something, but I think this might just be an error with the parentheses.

             

            Seems like you want ( [Payed] + [Ins Payed] ) / Net

             

            In your row level example, the record has Payed = 29, Ins Payed = 0 and Net = 29

            And you're expecting it to yield 100%.

             

            29 + 0/29 = 29

            But (29 + 0) / 29 = 1.00 (100%)

            1 of 1 people found this helpful
            • 3. Re: Percentage not calculating correctly
              Char Miller

              Thank you both -

               

              Mark, the parenthesis were indeed wrong - thank you. I think what I need next is some help figuring out how to deal with the nulls and/or zeros. Because it's aggregated by avg., I end up with 77% instead of 69%, which is what the actual payments/net would be.

               

              I have the calculation wrapped in a ZN function. Is there something else I need to do to account for nulls?

               

              Data behind the 77% is attached.

               

              Shivaram, I tried updating the calculation per your formula and, unfortunately, got lot of red! Screenshot below.

               

              Thanks again for the help,

              Char

               

              Updated calc field;

               

              Avg. is 77% when s/b 69%

               

              Data:

               

              Very angry Tableau viz:

               

              • 4. Re: Percentage not calculating correctly
                ShivaRam Chennapragada

                The calculation itself is valid, so could you try replacing the measure values?

                • 5. Re: Percentage not calculating correctly
                  Char Miller

                  It worked!

                   

                   

                  So, to be sure I understand: the calculation is summing each individual column first, then averaging?

                   

                  Thanks so much!

                  • 6. Re: Percentage not calculating correctly
                    ShivaRam Chennapragada

                    Yes, each SUM ensures that individual rows for Payments and Net are aggregated individually before averaging.

                    1 of 1 people found this helpful
                    • 7. Re: Percentage not calculating correctly
                      Mark Holtz

                      Maybe a bit more for your edification in case you didn't know already:

                       

                      Often when doing calculations involving multiple measures (e.g., A as a % of B), you need to consider whether you will perform the calc at the row level and THEN aggregate the result

                      like AVG[A/B]  (***SUM in this case usually wouldn't make sense--you'd just end up with the SUM of all the individual % calculations of each row)

                       

                      or if you want to aggregate A and THEN divide by an aggregate of B (e.g., SUM(A) / SUM(B) )

                      When you pre-aggregate within the definition of a calculation, using that calculation now only allows the AGG measure calculation when you drag that measure onto a Tableau View.

                       

                      If you had A/B as a calculated field and dragged it onto your view as SUM([CalcField]), and then you changed the definition of [CalcField] to become SUM(A) / SUM(B), that's when Tableau gives you the "angry red pills." You are no longer allowed to represent SUM([CalcField]) but instead should re-drag the measure onto the view and will see Tableau refer to it as AGG([CalcField]). That's your clue that the definition of the calculation is already performing some sort of aggregation that could extend beyond the "row level."

                      1 of 1 people found this helpful