5 Replies Latest reply on Sep 5, 2016 7:29 AM by Carl Slifer

    How to do a % of calculation of two columns.

    Giulia BG

      Hi! I currently am trying to work on a tableau file, with the values for both "planned spending" and "actual spending". In the Excel spreadsheet, it is set up with two columns, one saying whether it's planned or actual, and one with the actual amount of money.

       

      In tableau I now have two columns, one with the actual value and one with the planned value, and i'm trying to calculate the what percentage of the planned value the actual value is, and I want the percentage to be shown in a new column.

       

      Does anyone have any idea of how to calculate this? Thank you!

        • 1. Re: How to do a % of calculation of two columns.
          Jusran Mawardi

          you can create calculated field = actual/planed. and change this calculated field format to percenteage.

          • 2. Re: How to do a % of calculation of two columns.
            Carl Slifer

            Hi Giulia,

             

            Can you make this calculated field?

            SUM([actual spending]) / SUM([planned spending])

             

            Then whatever dimension you use this % will be accurate.  You can create it to show always as a percentage by doing the following.

            1) Create the calculated field as above

            2) Right click the measure in the measure pane

            3) Choose default properties

            4) Go down to number format

            5) Choose percentage

             

            Cheers!

            Carl Slifer

            InterWorks

            • 3. Re: How to do a % of calculation of two columns.
              Giulia BG

              Hi! Unfortunately that doesn't work, because the actual spent amount is only in one column, and tableau won't let me differentiate between actual amount spent and planned amount. I've tried creating sets and doing calculations through those, but that doesn't seem to work either, because the sets use boolean data!

              • 4. Re: How to do a % of calculation of two columns.
                Akriti Lal

                You would need to create a Blend with the same Data set i.e. you will need to duplicate the data source then go to "Edit Relationship" and define the condition . There after you will be able to achieve what you are trying to .Try this out and let me know if it works.

                • 5. Re: How to do a % of calculation of two columns.
                  Carl Slifer

                  Hi Giulia,

                   

                  I misread your issue. Sorry I was mobile.

                   

                  Jusran was the closest here. Try this calc

                   

                  SUM(IF [Type] = 'Actual Spending' THEN [Value] ELSE 0 END)

                  /

                  SUM(IF [Type] = 'Planned Spending' THEN [Value] ELSE 0 END)

                   

                  This will look through every row and if the type is actual spending it returns the value else it is 0 and then it sums that up and divides it by the same but for planned spending.

                   

                  Then for each field it will give you the correct %. For instance if your data had another column that said Region as a header and the members would be something like USA, USA, EMEA, EMEA. So when you put my above calc in the view and this region field in the view as well you will get the resultant % for both regions.

                   

                  Best Regards,

                  Carl Slifer

                  InterWorks

                   

                   

                  Past this you will need to share sample data or be entirely more descriptive of your data structure and of your desired output.

                  1 of 1 people found this helpful