I want to create a column that contains the total sum of another column.

kav_100

Hi everyone, looking to create a column that contains total revenue in order to find individual contributions. I have tried to do this using rank/window function within the magic ETL however all I get is a running total.

Any help appreciated :) Thanks!

MichelleH

@kav_100 I'd suggest using a Group by Tile to find the totals, then joining that back to your detail data

Ashleigh

@kav_100 you can also try to use fixed functions in a beast mode, its like doing a group by but in a card so you keep all your granularity.

https://domo-support.domo.com/s/article/4408174643607?language=en_US

ST_Superman

The Group by tile will work if you want to perform this calculation in ETL. However, the downside there is that the total will be calculated based on the logic that you build into the ETL. For example, if you partition your totals to give you the total monthly sales you would not be able to change the data in a visualization to show weekly sales. Also, if you wanted to exclude a particular product or region, you would need to determine that prior to the group by tile and would not be able to change that decision at the card level.

I would suggest using a calculated field and a variable for this. Something like:

Variable 1:

Create a variable that would allow you to select the metric that you want to partition the data by. Either Month, Year, Region, Manager, etc.

Then create a beastmode to use that variable:

SUM(SUM(`Sales`)) FIXED (BY `Variable1`)

This would allow more flexibility in terms of how you are totaling the sales. Also, by using a calculated field, if you decide to filter out a product or region, The calculation will respond to those filters and update the value in your card.

MichelleH

@kav_100 I'd suggest using a Group by Tile to find the totals, then joining that back to your detail data

Ashleigh

@kav_100 you can also try to use fixed functions in a beast mode, its like doing a group by but in a card so you keep all your granularity.

https://domo-support.domo.com/s/article/4408174643607?language=en_US

ST_Superman

The Group by tile will work if you want to perform this calculation in ETL. However, the downside there is that the total will be calculated based on the logic that you build into the ETL. For example, if you partition your totals to give you the total monthly sales you would not be able to change the data in a visualization to show weekly sales. Also, if you wanted to exclude a particular product or region, you would need to determine that prior to the group by tile and would not be able to change that decision at the card level.

I would suggest using a calculated field and a variable for this. Something like:

Variable 1:

Create a variable that would allow you to select the metric that you want to partition the data by. Either Month, Year, Region, Manager, etc.

Then create a beastmode to use that variable:

SUM(SUM(`Sales`)) FIXED (BY `Variable1`)

This would allow more flexibility in terms of how you are totaling the sales. Also, by using a calculated field, if you decide to filter out a product or region, The calculation will respond to those filters and update the value in your card.