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.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 695 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive