Sum x for each unique y

Charlie Bantoft

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

ColemenWilson

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`))

Ashleigh

@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. https://domo-support.domo.com/s/article/4408174643607?language=en_US

marcel_luthi

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.

ColemenWilson

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`))

Ashleigh

@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. https://domo-support.domo.com/s/article/4408174643607?language=en_US

marcel_luthi

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.