Count Max(value) by each ID

[nudjaree1]

Hi, 

I am trying to get Count for Max(Value) 

This is my dataset 

ID	No	Value
1	1	11
1	2	30
1	3	30
2	1	40
2	2	40
2	3	10
3	1	10
4	1	10

 and I want the result be like this 

ID	Max Value	Count Max Value
1	30	2
2	40	2
3	10	1
4	10	1

I try to use this count({<Value = {'$(=max(Value))'}>} No)

but I got 0 in the result 

ID	Max Value	Count Max Value
1	30	2
2	40	0
3	10	0
4	10	0

Expression evaluated show only 30 for the Max (value) 
count({<Value = {'30'}>} No)

Is it possible to get the first row number of the Max(Value)?

Thank you in advance.

[marcus_sommer]

You may try: count({<Value = {"=Value=max(Value)"}>} No) 

whereby it's not a real set analysis anymore else it worked like an if-loop.

- Marcus

[nudjaree1]

Hi Marcus, 

Thank you for the answer but  I got 8 for every ID

Max Value	Max Value
1	30	8
2	40	8
3	10	8
4	10	8

Regards 

Nudjaree

[MayilVahanan · Accepted solution]

Hi @nudjaree1 

May be,try like below

Sum(Aggr(if(Value = Max(Total<ID>Value), 1, 0) , ID, No))

[nudjaree1]

Thank you MayiVahanan.  It works 🙂 

[nudjaree1]

Is it possible to get the first NO of Sum(Aggr(if(Value = Max(Total<ID>Value), 1, 0) , ID, No))

ID  MAx  Count No

1   30       3         2

2   40     3           1

3     10    1           1

4   10     1            1

[CHRIS_Singa]

Hey @nudjaree1 ,

Would you tell me how you have done to get the Max Value for each ID ?

Please,