I want to create a column that contains the total sum of another column.
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!
Best Answers
-
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.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman2
Answers
-
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.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman2
Categories
- All Categories
- 1.1K Product Ideas
- 1.1K Ideas Exchange
- 1.2K Connect
- 969 Connectors
- 258 Workbench
- Cloud Amplifier
- 1 Federated
- 2.4K Transform
- 76 SQL DataFlows
- 501 Datasets
- 1.8K Magic ETL
- 2.7K Visualize
- 2.2K Charting
- 378 Beast Mode
- 20 Variables
- 486 Automate
- 104 Apps
- 378 APIs & Domo Developer
- 6 Workflows
- 22 Predict
- 6 Jupyter Workspaces
- 16 R & Python Tiles
- 317 Distribute
- 65 Domo Everywhere
- 252 Scheduled Reports
- 59 Manage
- 59 Governance & Security
- 1 Product Release Questions
- 5K Community Forums
- 37 Getting Started
- 23 Community Member Introductions
- 64 Community Announcements
- 4.8K Archive