3 Replies Latest reply on Dec 19, 2018 5:01 AM by Ken Flerlage

# Calculated Field for set of measures

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

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.

• ###### 2. Re: Calculated Field for set of measures

Hi Ken,

Sorry for my late reply.

Thanks a lot for your solution. Indeed the best way was to pivot the data and use it in tableau.

Your solution works perfect! It indeed represents a lot of effort when you have several group of measures and types of budget but for a smaller case it works nice.

Thanks again!,

Diego

• ###### 3. Re: Calculated Field for set of measures

Great. Would you be so kind as to mark my response as the "correct answer" so we can close this thread and others can easily find the answer to similar questions? Thanks!