Sum x for each unique y
Hi all
I'm hoping you lovely people can help me sort the following out, please
I have tens of thousands of rows of data which all repeat themselves (for boring reasons). There are 20+ columns but two are always a pair. I've used 'country' and 'value' here to demonstrate (see table below).
All I would like to do is add up the value for each unique (distinct) instance of the Country. The value is always the same for each instance of the country.
I've got a rough workaround with SUM(DISTINCT Value
) but some countries have the same value - so, with this solution, their individual values get lost and the total is not accurate.
All thoughts welcome.
Many thanks
Country | Value |
---|---|
UK | $786.25 |
UK | $786.25 |
UK | $786.25 |
UK | $786.25 |
UK | $786.25 |
UK | $786.25 |
UK | $786.25 |
USA | $253.32 |
USA | $253.32 |
USA | $253.32 |
Japan | $780.23 |
Germany | $23.20 |
Germany | $23.20 |
Germany | $23.20 |
Germany | $23.20 |
Germany | $23.20 |
Best Answers
-
If you are doing this in Magic ETL you would use the Group By tile and group by `Country` and then choose SUM as the aggregate. If you are doing this in a table card, all you have to do is bring the 2 fields into the table card and then sum the `Value` field and it will auto group by unique instances of the other field. If you are doing this in a beastmode for other chart types you would use a fixed by function: SUM(SUM(`Value`) FIXED (BY `Country`))
If I solved your problem, please select "yes" above
0 -
@Charlie Bantoft here is more detail on how to do Fixed Functions. I find fixed functions allow for more flexibility then an ETL, especially if you want to do filtering on other fields.
**If this answer solved your problem be sure to like it and accept it as a solution!
1 -
As @Ashleigh mentioned, going with the FIXED function is likely the simplest thing. Something along the lines of:
SUM(AVG(`VALUE`) FIXED (BY `Coutry`))
Could do the trick, this tells the system to average at the country level, and SUM for anything after, so if your card you do a group by country it should show you what I think is the desired output.
0
Answers
-
If you are doing this in Magic ETL you would use the Group By tile and group by `Country` and then choose SUM as the aggregate. If you are doing this in a table card, all you have to do is bring the 2 fields into the table card and then sum the `Value` field and it will auto group by unique instances of the other field. If you are doing this in a beastmode for other chart types you would use a fixed by function: SUM(SUM(`Value`) FIXED (BY `Country`))
If I solved your problem, please select "yes" above
0 -
@Charlie Bantoft here is more detail on how to do Fixed Functions. I find fixed functions allow for more flexibility then an ETL, especially if you want to do filtering on other fields.
**If this answer solved your problem be sure to like it and accept it as a solution!
1 -
As @Ashleigh mentioned, going with the FIXED function is likely the simplest thing. Something along the lines of:
SUM(AVG(`VALUE`) FIXED (BY `Coutry`))
Could do the trick, this tells the system to average at the country level, and SUM for anything after, so if your card you do a group by country it should show you what I think is the desired output.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive