10 Replies Latest reply on Oct 25, 2018 10:14 AM by Akram Ebrahim

    Calculating with Percentages

    Katelyn P

      Hello,

       

      I am using two data sources that are not joined/linked in my Tableau workbook.  Both data sources come from the My SQL Server.

       

      The one data source gives me all answered call data.  The second one gives me all unanswered call data.

       

      I created a calculated field that allows me to add up both answered and unanswered calls to get the total amount of calls that occurred on any given day.

       

      My formula for total calls is: [Answered Inbound Calls (SQLDatabase)].[Answered Inbound Calls]+[Unanswered Inbound Calls]

       

      I want to be able to view what the percent of answered calls is compared to unanswered. 

       

      When I try to create a calculated field for Percent Answered I use the following formula: [Answered Inbound Calls (SQLDatabase)].[Answered Inbound Calls]/[Total Calls]

       

      However, that percentage calculation always gives me 100% and the Percent Unanswered calculated field gives me 0%.

       

      I'm not understanding why Tableau is not giving me the correct percentages.

       

      I feel like this is such an easy thing to calculate and maybe I am overthinking how to do it.

        • 1. Re: Calculating with Percentages
          Akram Ebrahim

          Percent Calculated field:

           

          1.Please ensure you have set default properties as percentage for this calculated field

          Default Properties-> Number format -> Percentage.

           

          If not solved then,

          2. Modify the calculated field like

          ([Answered Inbound Calls (SQLDatabase)].[Answered Inbound Calls]/[Total Calls])*100

           

          even not solved then,

          3. keep [Answered Inbound Calls (SQLDatabase)].[Answered Inbound Calls] as different measure and see value coming or not

           

          4. Please check if value in [Total Calls]  is not zero

           

           

          • 2. Re: Calculating with Percentages
            Rob Palmer

            Hello Katelyn,

             

            One question, why are you using two separate data sources? You mention that these are stored in your SQL server so are you able to build a view that has both sets of data in and a flag that shows if the call was answered or not?

             

            In the long run this approach is going to give you something that's more efficient then the method above, and it's going to be more flexible as it'll allow you to do analysis on answer rates based on time of day, day of week, etc.

             

            Personally I only create calculated fields like this, or undertake data blending in very limited situations and avoid it if I can.

             

            Cheers

            Rob

            • 3. Re: Calculating with Percentages
              Katelyn P

              The reason that these data sources are separate is because they are two different custom queries.

               

              Ideally, it would be awesome if I could put answered and unanswered data in one table and use one query, but the way that the data comes back, that option is not available unfortunately.

               

              I can't copy everything from the unanswered table and paste it below the data in the answered table in SQL.  Is this possible to do in Tableau?  Sorry if that's a silly question. I am new to using Tableau.

              • 4. Re: Calculating with Percentages
                Katelyn P

                I did change the Default Property to percentage and modified the calculation to ([Answered Inbound Calls (SQLDatabase)].[Answered Inbound Calls]/[Total Calls])*100 like you suggested.

                 

                It got closer to the result I wanted, but is still not completely accurate.

                 

                This was my result in my table:

                 

                Department: Customer Service

                Answered Calls: 700,385

                Unanswered Calls: 898

                Total Calls: 701,283

                Percent Answered: 9987%

                • 5. Re: Calculating with Percentages
                  Rob Palmer

                  If you are formatting as a percentage then you don't need to worry about the *100 as Tableau will deal with it for you. I would also change it to:

                   

                  SUM([Answered Inbound Calls (SQLDatabase)].[Answered Inbound Calls])/SUM([Total Calls])

                   

                  That way it tells tableau to calculate the totals depending on the dimensions in your report before it does the division. Otherwise Tableau will work out the division before it aggregates the base - which tends to result in the calculation being incorrect.

                   

                  From memory the only way to union to custom SQL components together into a single view is to write it as a SQL statement with a union or union all within it - which from above sounds like it isn't an option as they have different fields in.

                  • 6. Re: Calculating with Percentages
                    Akram Ebrahim

                    Hi Katelyn,

                     

                    If you change the default properties to Percentage then that is sufficient. Now you should get 99.87%

                    No need to multiply the calculation by 100. I have given the checklist not steps.

                    As Rob suggested you can try to combined these two queries with joins and we also have an option to directly paste SQL query as datasource in tableau.

                    Connect to a Custom SQL Query

                    • 7. Re: Calculating with Percentages
                      Katelyn P

                      When I try SUM([Answered Inbound Calls (SQLDatabase)].[Answered Inbound Calls])/SUM([Total Calls]) it gives me an error.  The whole field turns red and says that I can't use SUM since Answered Inbound Calls is already an aggregated field.

                       

                      When I don't multiply the calculation by 100, then I just get this as my result:

                       

                      Department: Customer Service

                      Answered Calls: 700,385

                      Unanswered Calls: 898

                      Total Calls: 701,283

                      Percent Answered: 100%

                      • 8. Re: Calculating with Percentages
                        Akram Ebrahim

                        Enable default properties percentage with 2 digit decimals

                         

                         

                        It coming 700385/701283 = 0.998720, so by default tableau will round if anything more the 0.5 decimal ( Ex. 99.6 will be considered as 100 where 99.4 will be considered as 99)

                        1 of 1 people found this helpful
                        • 9. Re: Calculating with Percentages
                          Katelyn P

                          Yeah, that worked.  I can't believe it was that simple of an issue and I overlooked it.  Thank you both for your help!

                          • 10. Re: Calculating with Percentages
                            Akram Ebrahim

                            You are welcome.Keep Learning. Cheers !