Summing a Max or Level of Detail
My data has row count information that is duplicated or reported on a Column Name basis. I need to be able to sum the max of each dataset name, or sum by level of detail - column name.
For example in the data shown below. The row count for 'KPI DOC DOE SUB BE - SBE as category' is 5,013,614,211 and the row count for 'MKTG - EVENTS DATA - EMAIL ECIDS - DEV' is 111. I want to report a Total Row Count in the Summary Number of 5,013,614,322, not the 145.4B that is currently being reported because it is summing up the entire of 'Row Count'.
Can this be done in a beast mode?
Answers
-
A basic way to accomplish this in a beast mode is to use sum distinct like this:
SUM(DISTINCT `Row Count`)
However, this is not a perfect solution if you have names with the same row count. I'm not sure it is doable in a beast mode that is perfect, but you could do this in Magic ETL by doing a group by statement to get the summary number total that you are looking for and then join that back to your main dataset. This will create another column with that summary number showing for every row in your dataset, but then you can just reference that number in your summary number and just choose max or average as your aggregation type.
Hope that makes sense.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
Thanks for the feedback. I think you are correct in that it will have to be done using ETL....I'll go that route. Thanks again.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive