How do I use the Col Maximum Formula with a "where" condition

[#1]

Hi, I have a problem where I want to formulate a new column as a benchmark to all other countries. If there are more rows for the country, I want to take the max value of it as the benchmark. See the example below for clarification.

Data kind of looks like this:
Country       Value         period       

A                  10              201301         

A                  30              201301          

B                  20              201301          

C                  50              201301

D                  40              201301

What I want to achieve (with cell formula): Col Maximum (:value, :period, :country) where :country == "A"

Output:

Country       Value         period        BenchMark

A                  10              201301          30

A                  30              201301          30

B                  20              201301          30

C                  50              201301          30

D                  40              201301          30

As you can see, I want to benchmark the max value of only country A.

I tried Col Maximum (:value, :period, :country == "A") and it will return "50"

Col Maximum (:value, :period, :country) and Col Maximum (:value, :period, :country == "A") both returns "50"

I am pretty sure there's a simple solution for this!

Hope you guys can help!

Thanks thanks!

[#2]

2025 - JMP19 comes with a significant speed improvement for string comparisons.
cool!

Besides that, the suggested 

col Mean(:weight, :age, where(not(excluded())))

was introduced for transform columns in reports. It got as compact as:

On the other hand, it's still very difficult to get the right formula for an  aggregation restricted to e.g.  :sex="M":

if(:sex=="M"& not(excluded()),
   col Max( if(:sex=="M", :weight,.), :age, excluded())
   , .
);

Something like

col Max(:weight, :age,  @excluded, where(:sex=="M"))

could help a lot.


Hm:  Col Median (and others): add "Where" option   : 1 Kudo in 3.5 years?! strange.