How to create a calculated column similar to pivot table in Excel?

[#1]

I want to use graph builder to slice and dice attrition data. In Excel I would use what is called a calculated field within a pivot table.

My data would look like the attached, where I can select any field as row(s)s or column(s) and the attrition rate would update dynamically since its a caculated field.

How do I do this in JMP? I tried HC/TER and sum(HC)/sum(TER). I did this in the data detail as as a separate column. Neither worked since they only caculate once at the individual level. I need something that will

perform the caculation on update.

[#2 · Accepted solution]

I don't think JMP can do exactly that, with an arbitrary formula evaluated over the dynamic grouping.

If you know the grouping and can create a formula, be sure to use the Col functions and provide the grouping column(s) as BY variable(s):   Col Sum( :HC, :KeyEE ) / Col Sum( :TER, :KeyEE )

Otherwise you can compare sums (inferring the ratio) with a table in Tabulate:

Or graphically in Graph Builder: