I have a set of data with IDs having multiple entries. I would like to take the max value per ID, and base on another category (bin), determined by the value of that max value, I would like to group together all similar max values within that same bin and add them together, to do further calculations such as avg on them. I'm not sure if I could write a single measure for it or would need to break it up into multiple steps.
Example data:
ID with mult values per ID / Category to filter by
1 99 A
1 100 A
1 101 A
2 0 B
2 50 C
2 100 A
Base on data above, I want to take the max value from ID 1 (101) and add that to the max value from ID 2 (100), since they are both in the same category.
I would ignore all the other values from ID 1 and 2, since they are not max values, and for the other categories (B, C, etc), I would ony want to add values within that bin only if they are the max value for their unique ID.
Help and tips is appreciated. TYIA
