Summarize Max value over group

Hi all!

I have not perfect dataset, with duplicated values. This is example of what i have and what i need:

To get correct SUM of revenue, first i need get MAX(or min, the values are same inside group) grouping by Company and Division, and only after that i can SUM this values to get correct result.

Help me please to write correct measure to avoid that duplicates.


Hi @iskorokhid
you can use MAX() or AVG().

Something like.

On company level it will be a sum of the new field.

Thank for feedback! I tried SumOver before, here your sample:

SumOver works only if i put field, that i use in formula to table, but it’s not working when i use 1 field, or some other fields.

Is there a solution to show correct values in any combinations of columns?

What if changing the aggregation of Revenue?

If you mean just make revenue as MAX - it will show Max value, SumOver has custom aggregation so it’s not possible to change it.

i mean in Rows “Company” and “Division” and Value Revenue(max)

In the end I don’t need any rows, i need measure that can work without that columns, for example - to use it in KPI chart:

Looks like it’s not possible in QuickSight, or i can’t find how to do that trick : (

Based on this description, the following calculated field should work for you:

sum(min(Revenue, [Company, Division]))

That’s why i liked your post in another topic! It’s works and it’s pure magic : ) Thanks a lot!

@iskorokhid, I’m glad it worked.

You can read more about how to deduplicate data here:

I’ve been banging my head against the wall for 3 days trying to find a solution to this exact problem. In the end, so simple. :slight_smile: Thank you!