4 Replies Latest reply on Jan 13, 2017 12:50 PM by Evan Campbell

    Add Two Columns of Data Together from Same Dimension

    Evan Campbell

      I have a table where the Rows are represented by [Year] and the Columns are represented by [Stages]. The measure sums up the cost for each year for each stage. I would like to build another column of data that adds the first 2 stages together. Is it possible to do this with one dimension/measure? How could this be done?

       

      Thanks!

        • 1. Re: Add Two Columns of Data Together from Same Dimension
          Stephen Rizzo

          I am not sure what exactly you are trying to do. If you want to replace the first two stages with a combined first and second stage, then you can simply select stage 1 and stage 2, right-click on the dimension value in the header, then select "Group". That will create a combined "Stage 1 & Stage 2" value replacing those columns. If you want to add a column with the first two stages in addition showing to the distinct stages, that gets a bit more complicated, as that requires double-counting specific rows.

          • 2. Re: Add Two Columns of Data Together from Same Dimension
            Evan Campbell

            Hey Stephen,

             

            Thanks for the reply. I'll clear up the confusion. I have a table of values. The rows are [Year] and the columns are [Stages]. The [Stages] I have filtered down to the first 3 stages: 1.0 DEVELOPMENT, 2.0 PRODUCTION, 3.0 ANALYSIS.

             

            I have the cost associated for each stage and year. I want to create another column that will add the first two stages values.

             

            For example: Lets say in 2010 the cost for 1.0 DEVELOPMENT was $10. and in 2010 the cost for 2.0 PRODUCTION was $20. I want a third column that will add the two values together and return $30.

             

            Hopefully that helps!

             

            Thanks!

            • 3. Re: Add Two Columns of Data Together from Same Dimension
              Stephen Rizzo

              That does help. Unfortunately what you are trying to do, although it sounds quite simple (and would be quite simple to do in a spreadsheet), is a bit more complicated than it seems.

               

              The problem here is that by putting [Stages] in the columns shelf and [Year] on the rows shelf, you are telling Tableau that you want your tabular data organized such that each row in your table gets assigned a year, each column gets assigned a stage, and the values in the table are the costs associated with the relevant year and stage combination. Your third column does not match that structure, so you will need to change the structure of your table or your data.

               

              Here are a handful of ways that you could make this work:

               

              • If you only need to display the first two stages, you could display the third column as the total
              • You could create calculated fields for each stage cost, then create a new calculated field for the sum of the first two stage costs
              • You could display the sum of the costs for stage 1 and stage 2 as a single column table (headers hidden) in a dashboard directly adjacent to your first table
              • You could pivot your data so that each stage cost is its own measure, then create a new calculated field for the sum of the first two stage costs
              • You could add a stage in your data called stage 1 and 2 that combines the cost of the first two stages

               

              Of all of these options, the second is easy to implement and probably is the closest to what you were hoping for. It would look like this:

               

               

              With the calculated fields defined as

               

              [Stage 1 Cost] : IF [Stage] = 1 THEN [Cost] END

               

              And the Stage 1 and 2 Cost defined as

               

              [Stage 1 and 2 Cost] : ZN([Stage 1 Cost]) + ZN([Stage 2 Cost])

              1 of 1 people found this helpful
              • 4. Re: Add Two Columns of Data Together from Same Dimension
                Evan Campbell

                That was so much help!

                 

                You helped me solve it! Thank you so much!