4 Replies Latest reply on Feb 18, 2016 5:36 AM by Mark Fraser

    Calculated Field Addition Question

    Tony Ball

      Hi All,

       

      I hope you can help with this one. Apologies if this is simple to sort out but it has me a bit stumped.

       

      I'm trying to create a calculated field that will add up  the values in a measure field called Watts Budget.

       

      In the example screenshot below I want to add up 2400 + 2400 in Watts Budget to give me another column called Total Watts where I would like a value of 4800 to be present.

       

       

      I've been trying to write something like this but my calc field is getting confused due to use of dimension string values and measure integer values.

       

      IF [Port Name] = "PS1" AND "PS2" Then [Watts Budget] + [Watts Budget]

      END

       

      Please can someone help or point my right direction? It will be very much appreciated if you can.

       

      Thanks

        • 1. Re: Calculated Field Addition Question
          Mark Fraser

          Hi Tony

           

          If my explanation isn't helpful, hopefully this is > Understanding Logical Calculations | Tableau Software

           

          This is quite possible, you just need to do it slightly different, Tableau doesn't work quite like Excel in this regard.

          I have mocked something up in Superstore to try and help demonstrate

          Here i only want the sales for 2 countries, Italy and Ireland.

          I use an IF statement, but combine with an OR not and.

           

          Why dont I use AND?

          You will note that each country is individual, i.e. there is only one country per line,

          There isn't the combination Italy & Ireland, so the formula would always fail, it would never pass.

          by using OR we now are doing 2 tests, of which each can individually pass/ fail, we''ll then SUM the results of those 2 tests.

           

          You will note I only reference Sales once, and I don't SUM it

          The SUM is done, when i add the field to the view


          Sorry, I am doing a cr@p job explaining, but i hope between the screenshots, the link and some of what i wrote, something makes sense!

          feel free to come back if anything isn't clear

           

          Cheers

          Mark

          1 of 1 people found this helpful
          • 2. Re: Calculated Field Addition Question
            Ben Neville

            First, let's address your calculated field. That calculation will evaluate at the row level. The first portion is not a valid syntax in Tableau, as you need to specify your field again. It would more correctly be written something like:

            IF [Port Name] = "PS1" AND [Port Name] = "PS2" THEN [Watts Budget] + [Watts Budget] END

             

            However, this still will not return what you are after. As I mentioned, this calculation is evaluated at the row level, and for a single row, the Port Name value can never be both PS1 and PS2, so you need to change how you are computing this.

             

            There are a couple ways to solve what you're after.
            Option 1 would be turning on sub/grand totals. This should show you a row below these values with the total.

            Option 2, if you're set on having another column, the current "calculation" returning your Watts Budget is likely SUM([Watts Budget]). Add a new column which is WINDOW_SUM(SUM([Watts Budget])) and correct your compute using to Model Name-->Subclass-->Port Name and restart every Subclass.

            Option 3 would be to use a LoD expression. Use something like: {FIXED [Model Name], [Subclass]: SUM(Watts Budget)}

             

            See more about LoD expressions here: Introduction to LOD Expressions | Tableau Software

            1 of 1 people found this helpful
            • 3. Re: Calculated Field Addition Question
              Tony Ball

              Hi Mark, Ben,

               

              thanks for your help on this one really appreciate it. The Window Sum option sent by Ben solved my specific problem but I've learnt a lot from both responses. So thank you for taking the time to send them.

              • 4. Re: Calculated Field Addition Question
                Mark Fraser

                Tony, you're very welcome, I to enjoyed reading Ben's solution, and I'm pleased you found something suitable for your particular issue.

                What we are describing above, should give you a really good base to work from in future with regard to table calculations - and of course, if you get stuck, we're always here to help!