Calculated value - Sum per category

[Sébastien Medhat 2]

Hello,

I have the following table values :

A B

CAT1 20

CAT1 20

CAT2 40

CAT2 40

CAT3 20

CAT3 20

 

I would like to sum in a calculated value the values in column B per CAT, the final result should be in the following example to 80 (20 + 40 + 20). I tried the OVER function and Intersect, but can't find the right way.

It's been a half a day that I'm trying to figure it out.

Thank you for your precious help.

[Fabian Duerr]

Do you want to sum the unique values per category

 

What if for example you have another CAT1 with 30 (instead of 20)

[Sébastien Medhat 2]

Hello,

 

Yes, sum of the unique values per category. For each categrory I only have unique values. 

 

Thank you.

[Fabian Duerr]

Alright, since you are having unique values for each CAT you can use this:

Sum(If(Rank([A],[A],"ties.method=first")=1,,NULL))To understand what is gonig on here you can add twocalculated columns:

Rank([A],[A],"ties.method=first")This will rank each row within a category

If(Rank([A],[A],"ties.method=first")=1,,NULL)This will make sure that you only keep the 1st row within each catogory.

Finally, you Sum() all thoseremaining values

[Sébastien Medhat 2]

Hello,

Thank you for you answer and your explanation, it works! :)

Best regards,