4 Replies Latest reply on Apr 6, 2016 7:26 AM by Andrew Watson

    Table calculation across multiple measures

    Daniel Safai

      Hi Tableau community,

       

      I have some trouble creating a calculated field that works across dimensions and measures.

       

      In the example I have three measures split in two years by the "year" dimension. How do I calculate the percentage difference from year to year for each row as an additional column?

       

      The dashboard in the file mimics the resulting table I want.

       

      Thank you in advance.

       

      Kind regards

        • 1. Re: Table calculation across multiple measures
          Tharashasank Davuluru

          Hi Daniel,

          Just change your representation and apply percent difference table calculation on every measure.

          check this:

          • 2. Re: Table calculation across multiple measures
            Andrew Watson

            You're trying to do one of those things that seem like they should be simple but they're not. First I suggest reshaping your data. Depending on your version of Tableau this is possible to do in Tableau.

             

            Edit your data source and select the 3 Value X columns and Pivot these

             

            From there it's simple to produce your initial  table:

             

             

            However the YoY calc as one column is more complex. Tableau will create your Percent Difference calculation for you doing a Quick Table Calculation. Adding this gets you part way to where you want to be:

             

            Creating a flat table with 3 headers, 2015, 2016 and YoY is more complex and will require creating calculated fields.

             

            Create a field called 2015 with this formula: if [Year] = '2015' then [Pivot field values] END

             

            Do the same for 2016. Use these fields to create the Yoy calc SUM([2016])/SUM([2015]) - 1. Then you can put it together and get the following:

             

            • 3. Re: Table calculation across multiple measures
              Daniel Safai

              Hi Andrew,

               

              Thank you so much for your quick answer. However, I forgot to mention that I want to do this without reshaping my data as i need the varibles for some other calculations.

               

              Your anwer was totally correct given my explanation, but do you by any chance know how to do the exact same thing without reshaping the data?

               

              Kind regards,

               

              Daniel

              • 4. Re: Table calculation across multiple measures
                Andrew Watson

                You could reshape and then recreate your fields Value A, B & C. This formula would recreate Value A:

                 

                IF [Pivot field names] = 'Value A' THEN [Pivot field values] END