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.
Thanks!
Hi @iskorokhid
you can use MAX() or AVG().
Something like.
avgOver(max({Revenue}),[{Company},{Devision}])
On company level it will be a sum of the new field.
BR
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. 
Thank you!