9 Replies Latest reply on Apr 9, 2012 2:23 AM by Ranjan Sahoo

    Two different column values needs to shown in pie as 100%

    Ranjan Sahoo

      Hi,

       

      I have one column value as 20 and another as 60. I need to display both columns in a single pie chart with total as 100%.

       

      I have added both to 80 and then caluculated as percentage as 25% and 75%, but i am unable to display as both are differnt column.If i can do the grouping to a single entity in Tableau then both can be displayed, but i am unable to group to single entity.

       

      Can anybody help me?

       

      Regards,

      Ranjan

        • 1. Re: Two different column values needs to shown in pie as 100%
          Jonathan Drummey

          Hi Ranjan,

           

          See the attached for details. Measure Names is used to set the color, while the angle is set by Measure Values where the measures used are the two % of total measures.

           

          Jonathan

          • 2. Re: Two different column values needs to shown in pie as 100%
            Ranjan Sahoo

            Hi Jonathan,

             

            Due to firewall, i am unable to open today, i can open up on moday only.

             

            From your reply what i can see you are tryin to tell measures to be set in angle meaure names to be in colour.

            I have two measures only to get displayed measure1,measure2. But only one can be put in angle and there was no as such dimension is there.

             

            Although i have done through union of two measures pushing to a single column using costum sql, i have done.

             

            q. was         m1  ,  m2

                              20       60

             

            what i have done      M      Value

                                          m1     20

                                           m2    60

             

            Then i used M and Value to resolve the problem.

             

            Please let me know if you have any better solution.

             

            Thanks for your help and suggestion.

             

            Thanks,

            Ranjan

            • 3. Re: Two different column values needs to shown in pie as 100%
              Jonathan Drummey

              Hi Ranjan,

               

              The Custom SQL you created to reshape the data is definitely one way to do this. The method I described used the Measure Names/Measure Values variables and doesn't require reshaping. With Measure Values on the Angle shelf, each value coming from a measure on the Measure Values card determines the angle for a section of the pie. With Measure Names on the Color shelf, the colors comes from the set of measures on the Measure Values card. Think of Measure Names/Measure Values as convenience variables that let you access a set of measures at once, they can work around all sorts of issues in wide data like the one you described.

               

              In the screenshot below, the Column1 % calculated measure is simply [column1]/([column1]+[column2]) from the data, where column1 had a value of 20 and column2 a value of 60.

               

               

              screenshot1.jpg

               

              The reshaped data is often easier to work with in Tableau, on the other hand it requires more ongoing attention because a new field added to the data source will require editing the Custom SQL. So, either method can get you the results.

               

              Jonathan

              • 4. Re: Two different column values needs to shown in pie as 100%
                Ranjan Sahoo

                Hi Jonathan,

                 

                Really though provoking answer, Even i don't like to hit again DB for the same.I am very new to Tableau, again having trouble to understand you.I will certainly see your *.twbx in Monday.

                 

                Q:              m1  ,  m2 

                                  20       60

                 

                I have reached m1 and m2 after long calculation, not a direct derivative of table. As i understand, whether m1 and m2 both can be dropped to calculation and 20 and 60 can be dropped to angle shelf.I am unable to drop both on the same shelf.

                 

                 

                Can you please help me by describing in the following name m1 and m2. Really it will help me in solving all other problems, i won't require transpose.

                 

                 

                Thanks,

                Ranjan

                • 5. Re: Two different column values needs to shown in pie as 100%
                  Jonathan Drummey

                  Hi Ranjan,

                   

                  In my above description and the attached workbook m1 = calculation2 and m2 = calculation1.

                   

                  I first suggest you read this knowledgebase article, which talks about how Measure Names and Measure Values get us around the issue of only being able to put one measure on a shelf: http://kb.tableausoftware.com/articles/knowledgebase/measure-names-and-measure-values-explained

                   

                  So, in our case, Measure Values gets us (the calculated % of total fields of) m1/calculation2 and m2/calculation1 as two different values within the same "dimension" which is Measure Values, and we can put Measure Values on the Angle shelf and Tableau will use the two values. Measure Names gets us the names of the two measures within the same "dimension" which is Measure Names, and we can put Measure Names on the Color shelf, and Tableau will use the names of the two measures to create colors.

                   

                  Does that make sense now?

                   

                  Jonathan

                  • 6. Re: Two different column values needs to shown in pie as 100%
                    Ranjan Sahoo

                    Hi Jonathan,

                     

                    Thank you very much for your help, I think your answer is very sensible, I am going through knowledge base and and will study same and will analyze your report on monday, so that solution can be found out.

                     

                    I think it will help me.

                     

                    Last question of a different aspect, whether any measure will come within dimension in Tableau as i am seeing only there is a structural constraint.

                     

                    You are a star.

                     

                    Thanks,

                    Ranjan

                    • 7. Re: Two different column values needs to shown in pie as 100%
                      Ranjan Sahoo

                      Hi Jonathan,

                       

                      You are 100% correct,that will save me multiple hitting to my DB, but only thing is still i am unable to do. This is an imporant functionality.The reason behind i am unable to do is i am still unable to put 2 measures inside the color and angle.

                       

                      It will be a really help, if you define few steps, like how to drag the calculations in steps, so that multiple meausres will come to color and angle.take examp. of cal1 and cal2 to describe, really it will be a great help.

                       

                      Thanks,

                      Ranjan

                      • 8. Re: Two different column values needs to shown in pie as 100%
                        Jonathan Drummey
                        Hi Ranjan,
                        Here's a version of the workbook using your data. Here's the data:
                        m1     m2
                        20     60
                        I just copied and pasted this into Tableau, what shows up in Tableau is in the Data worksheet. From here, I created a new worksheet and did the following:
                        1. Set Mark type to Pie
                        2. Drag Measure Names onto Color shelf. A pie with one color shows up, the Measure Names color legend appears saying "No measure value"
                        3. Drag Measure Values onto the Angle shelf. There is now a Measure Values card. The pie is now colored, but includes SUM(Number of Records) as a measure.
                        4. Drag SUM(Number of Records) off the Measure Values shelf. The pie now shows only the two measures.
                        Does this make sense now?
                        Jonathan
                        • 9. Re: Two different column values needs to shown in pie as 100%
                          Ranjan Sahoo

                          Thanks a lot Jonathan.. It is done now.

                           

                          Thanks,

                          Ranjan