Tableau Public: What is the right way to create Calculated Fields?

Question

I am learning Tableau Public. Using version 2020.02.02 MAC version MWE ; Trying to create a new Calculated Field.

• use Samplestore dataset.

• In Data Source, drag the Orders table to Canvas.

• In Workspace, drag Profit dimension to Columns Shelf, and Categories measure to Rows shelf.

• In Data Pane, right click > Create > Calculated Field.

• In text field input 'PositiveProfit'.

• In text Area input expression:

  IF [Profit] > 0 THEN 1 ELSE 0 END

I expect when profit is greater than 0, the value of this new calculated field should be 1. right? Well I am getting something else. for example for Profit 18,451, PositiveProfit is 1,374.

What is the secret behind this? Any tutorials that explain?

Answer 1

This is likely because your calculated field is being aggregated. There are several ways to get around this.

Method 1

The easiest would be to click on PositiveProfit in the shelf and use the drop down menu to change it from Measure to Attribute.

Method 2

Another approach is to FIX PositiveProfit to your dimension. Fixed calculations are also known as Level Of Detail, or LoD calculations.

So, in your case, it sounds like you want 0 or 1 for each Category. That means you should fix your PositiveProfit to Category. So, create a new calculated field called PositiveProfitFixed:

{FIXED [Category] : MIN([PositiveProfit])}

And then place this new calculated field on the Columns shelf instead of PositiveProfit. Note: I'm using MIN but you could also use MAX. Both will return the same result because the formula will return either 0 or 1 and the min of 1 is 1 and the max of 1 is 1 (and same is true for 0).

Conclusion

The first approach I mentioned above is by far clearer than the second approach. And then are many other ways in which this can be achieved.

Answer 2

There is no error in your formula.

Actually you have calculated that how many times the profit was positive. See this screenshot

It may be clear that Profit $18,451 is sum of all profit values of all rows with category Furniture. There were 2,121 such rows (records). Out of these 2,121 records, 1,374 were the cases where profit recorded was positive and remaining cases were negative and thus, pertained to losses.

If instead you just want to calculate a field that just records that the profit you are showing is positive or negative, use this formula-

IF sum([Profit]) > 0 THEN 1 ELSE 0 END

This will give you an output like this

Or if you'll change the hierarchy in rows, then

Thus to sum it up, this is not a proper use case of LOD in tabbleau. However, you should know when to aggregate and when not.

P.S. In tableau you can automatically sort profit values and can create a chart like this

Answer 3

Change your formula to:

SUM([Profit])>0

This is a boolean formula, so will return True or False. If you need this to return 1 instead (perhaps you want to do another calculation on that, for example), wrap that formula in INT, where it will convert True to 1 and False to 0.

INT(SUM([Profit])>0)