1

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

JonathanCohen
  • 13
  • 4
  • 1
    You may want to try using a [FIXED Level of Detail Expression](https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod.htm) and wrapping your IF statement inside that. Something like `{FIXED [Activity_ID] : (your above if statement)}` – MUFF. Aug 26 '23 at 21:26
  • 1
    @MUFF. This worked! Thank you! – JonathanCohen Aug 28 '23 at 18:48

1 Answers1

1

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
Nicolaesse
  • 2,554
  • 12
  • 46
  • 71
  • 2
    I created the Max Cap and Max Customer calc fields and then used them in a third calc field and it work perfectly. MUFF's suggestion to add the FIXED LOD expression to my original calc field also worked. TY! – JonathanCohen Aug 28 '23 at 18:52