1 Reply Latest reply on Dec 6, 2018 2:22 PM by Ken Flerlage

    Calculated Field for set of measures

    Diego Roa

      Hi,

      I am trying to create a calculated field (difference between two columns) in the following context

      The rows are measures and I would like to calculate the difference between the budget and the actual for every measure on the rows.

      I haven't been able to calculate this difference as I have measures instead of dimensions and it doesn't work by transforming them into dimensions. Also, when I create a calculated field as another measure it will appear with  marketing and R&D and this is not the expected result.

       

       

      Attached you can find a sample workbook.

       

      Thanks a lot!

        • 1. Re: Calculated Field for set of measures
          Ken Flerlage

          This is a situation where it would be nice to be able to pivot rows to columns as we'd much rather have a separate measures for Actual and Budget with "Hard- und Software" and "Grid Connection" being within a dimension. We could then easily calculate the difference. That being said, there are ways to do this, but they're pretty hacky. If you are using a unionable data source, then you could do the following:

           

          1) Union the table to itself. This will essentially duplicate the data twice and will add an additional dimension called Table Name which will allow us to deal with each of the two copies differently. If you had more than 2 different types of budgets, then you'd have to union it that many times, which would be a pain.

           

          2) Create the following calculated fields which will sort of force the creation of a single dimension for the type and two measures for Actual and Budget.

           

          Type

          // Create a dimension for type.

          IF [Table Name]="Test_InputForTableau" THEN

              // This is our first table. Use this for Grid Connection.

              "Grid Connection"

          ELSE

              "Hard- und Software"

          END

           

          Actual

          // Create a measure for Actual.

          IF [Table Name]="Test_InputForTableau" THEN

              // This is our first table. Use this for Grid Connection.

              IF [Budget type]="Actual" THEN

                  [Grid connection]

              END

          ELSE

              IF [Budget type]="Actual" THEN

                  [Hard- und Software]

              END

          END

           

          Budget

          // Create a measure for Budget.

          IF [Table Name]="Test_InputForTableau" THEN

              // This is our first table. Use this for Grid Connection.

              IF [Budget type]="Budget" THEN

                  [Grid connection]

              END

          ELSE

              IF [Budget type]="Budget" THEN

                  [Hard- und Software]

              END

          END

           

          3) Then you can build your view like this:

          4) Now create your Delta calculated field:

           

          Delta

          // Difference between budget and actual.

          SUM([Budget])-SUM([Actual])

           

          5) Finally, put that on the Measure Values card.

          Like I said, it's a very hacky solution, but if you only have a few types, it'll work. The much better option, however, would be to restructure your data using some sort of data prep tool or find a way to get to the original source of the data which is likely not structured this way.

           

          Workbook is attached.