Issues with SUM Function within SQL Transformations

Hello everyone,
I've been encountering issues with the SUM() function within my SQL transformations. I've imported large datasets and added my own calculations (as shown in the attached photos). When I try to query the three mentioned columns and group the results by month, specifically using SUM(average meals...), SUM(total reimbursable...), and SUM(participation), my participation column returns values over 100%. However, the way the calculation is written, it should return a value less than 100% for each month.
Interestingly, when I pull the raw data with the individual row calculations (without using the SUM() function), my numbers are correct. I have another dataset that I'm using to compare my numbers, and the discrepancy only arises when I query with the SUM() function.
I'm unsure if I've written my calculation incorrectly, but it seems to follow the calculations that were provided to me. Any help would be greatly appreciated! I can provide more information if needed.
Comments
-
@martinez473 Would it be possible to see an anonymized sample of your data, as well of some examples of what you're seeing versus what you're expecting?
0 -
Hi, @MichelleH
I have attached two files, one for August 2024 and one for October 2024. I have also attached a picture of the pivot table that contains the correct numbers I am trying to match. This pivot table displays the grand total and extends to April 2025. Additionally, I have attached my Domo-created table, which goes up to March 2025. As you can see, the participation numbers are incorrect. Finally, I have attached a picture of my filtering within Domo's SQL Transformations, where I have removed unnecessary information.0 -
@martinez473 It looks like the issue is that you're calculating the percentage before rolling up on a monthly level, so your card is adding up the percentages of each row of your dataset. I'd recommend changing the Meals per Day and Participation to beast modes within your card that calculate percentages based off the totals instead of individual rows.
Meals per Day
SUM(`Total Reimbursable Meals`)/sum(`Serving Days`)
Participation
(SUM(`Total Reimbursable Meals`)/sum(`Serving Days`))/NULLIF(`20th Day`,0)
0
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 659 Datasets
- 116 SQL DataFlows
- 2.2K Magic ETL
- 816 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 82 App Studio
- 45 Variables
- 776 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 82 Workflows
- 23 Code Engine
- 40 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 1 AI Projects and Models
- 18 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 144 Manage
- 140 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive