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.
you can use MAX() or AVG().
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: