if-statementcalculated-columnstableau-desktoppane

Tableau: How to create a calculated field that has multiple conditions and only works against other values in the same pane

In the screenshot below you'll see that I have multiple Vendors with their own Activity_IDs. There is a calculated column (called Modified Capacity) that tries to replace the placeholder value of 1000 with the max value from the Customer Count column.

Screenshot of current Tableau layout

I've used the following Calc Field to get the pictured result but as you can see, the max value being returned is row-based and I need to find the max value based on Activity_ID.

IF MAX([Capacity]) = 1000

THEN MAX([Customer Count])

ELSEIF MAX([Capacity]) <> 1000

THEN MAX ([Capacity])

END

I appreciate any help.

I'm very new to posting so please excuse any rookie mistakes.

Jonathan

Solution

  • I suggest using LODs functions for this purpose.

    On your specifical problem, probably these three calculated fields would be helpful.

    Max capacity per vendor and activity

    // Max_capacity
{FIXED [Vendor],[Activity ID] : MAX([Capacity])}

    Max customer count

    // Max_customers
{FIXED [Vendor],[Activity ID] : MAX([Customer Count])}

    Modified capacity

    //Modified capacity
IF [Max_capacity] = 1000
THEN [Max_customers]
ELSE [Max_capacity]
END