7 Replies Latest reply on Jan 4, 2017 1:32 AM by Nagappan Nachiappan

    Need to find percentage

    Nagappan Nachiappan

      Hi All,

       

      Happy New Year to all of you ...

       

      I need to calculate the percentage in tableau for the following requirement.

       

      I have the table like below:

       

      cust_date           keyname               keyvalue

      2016-11-01      Customers                55

      2016-11-02      Customers                80

      2016-11-03      Customers                75

      2016-11-01      English_Speaking      30

      2016-11-02      English_Speaking      50

      2016-11-03      English_Speaking      25

      2016-11-01      Spanish_Speaking      25

      2016-11-02      Spanish_Speaking      30

      2016-11-03      Spanish_Speaking      50

       

       

      I need to do the following. Need to calculate the percentage for English and Spanish speaking customers. Also I need to find the grand total with correct the percentage value.

       

       

      Date                     Total_Customers      English_Speaking      Spanish_Speaking      %Of_Enlish_Speaking      %Of_Spanish_Speaking

      01 NOV 2016           55                          30                               25                               (30/55)*100                          (25/55)*100

      02 NOV 2016           80                          50                               30                               (50/80)*100                          (30/80)*100

      03 NOV 2016           75                          25                               50                               (25/55)*100                          (50/55)*100

       

      Grand Total                210                     105                               105                               (105/210)*100                     (105/210)*100

       

      Is it possible to do like this in tableau. Please give me some suggestions / points on this.

       

      Thanks,

      Nagappan N.

        • 1. Re: Need to find percentage
          Nikunj Bhardava

          Hi Nagappan,

           

          Here you go, PFA workbook, Please take a look on that.

           

          Hope this helps!

           

          Thanks,

          NIkunj

          • 2. Re: Need to find percentage
            Nagappan Nachiappan

            Dear Nikunj,

             

            Thanks for your prompt response.

             

            This is the solution exactly what I want. But one more additional problem is, I may get some new languages in the future. I can not able to restrict it to only two or three. Here the Language value is dynamic. That is why we keep them like key - value pair in the base table.

             

            How can i achieve this ? As per your solution, whether I need to create some more calculated fields for the new language values? For a dynamic data set, is it correct way to create / add more  calculated fields?

             

            Thanks,

            Nagappan N.

            • 3. Re: Need to find percentage
              Vignesh Ilangovan

              Hi Nagappan,

               

              You can have a two different sheet.

              First Sheet will have Actual value depending on N Languages

              Next sheet2 for % calculation -

              1. SUM(IF CONTAINS([Keyname],"Speaking") THEN [Keyvalue] END)  -> Percentage of Total

               

              Note: Even if it is have N languages then it will calculate the percentage. And then exclude customer Keyname in sheet2.

              In dashboard we can showcase in single row by hiding the Date field in sheet2

              • 4. Re: Need to find percentage
                Nikunj Bhardava

                Hi Nagappan,

                 

                If your Language is dynamic, then current solution is not too good. I created other solution in which you don't need to create %calculation for each language. its dynamic.

                 

                PFA workbook for your reference.

                 

                Hope this helps!

                 

                --Nikunj

                • 5. Re: Need to find percentage
                  Nikunj Bhardava

                  Hi Vignesh,

                   

                  Good try! Keep playing with the tableau.

                   

                  Creating separate sheet is not good option, what will you do if we have more records? you will get scroll in both the sheets and also it will not sync to each other when you scroll down to see other records.

                   

                  Thanks,

                  Nikunj

                  • 6. Re: Need to find percentage
                    Nagappan Nachiappan

                    Hi Vignesh,

                     

                    Thanks for your suggestions.

                     

                    Actually your solution is also correct. But my requirement contains a lot of dynamic values. If I used your solution then the dashboard does not good to see. But our ultimate aim is to make the reports / dashboards user friendly to customers know.

                     

                    Thanks for your effort . Thank you so much .

                     

                    With regards,

                    Nagappan N.

                    • 7. Re: Need to find percentage
                      Nagappan Nachiappan

                      Hi Nikunj,

                       

                      Thanks for suggesting a solution.

                       

                      Is it possible to group different measures and give them a name to it?

                       

                      Consider our example that we used. In that if I have different keynames and different measures to each keyname then is it possible to group measures under each keyname?

                       

                      Say, Keyname contains A,B,C. And I have some measures M1, M2, M3, M4, M5. And I want to show like

                       

                             A                  B                      C

                      M1      M2          M3               M4     M5

                       

                      With regards,

                      Nagappan N.