Summary number for non-distinct rows
Hi,
We have 4 sales regions & each has a budget amount. I need to do a summary number that uses the sum of those 4 budgets. 2 regions have the same budget amount so COUNT(DISTINCT) won't work. Each row of the dataset details an expense for a labeled region and that row also contains that region's budget amount (I joined the budget table with the sales table).
I have a card for each region that filters for just that region and I made a beast mode for the summary field to show the amount of budget they have used so far. This works fine for the individual region cards. But on a card I made (with no filter) to summarize all the regions together, the summary number goes to a crazy 674%. Is there a better way to get the usage % for all regions?
This is my current beast mode:
(CONCAT(ROUND(SUM(`ExpenseAmount`) /
((CASE WHEN `Region` = 'Northwest' THEN MIN(`Budget`) ELSE 0 END)
+(CASE WHEN `Region` = 'Southwest' THEN MIN(`Budget`) ELSE 0 END)
+(CASE WHEN `Region` = 'Northeast' THEN MIN(`Budget`) ELSE 0 END)
+(CASE WHEN `Region` = 'Southeast' THEN MIN(`Budget`) ELSE 0 END))
*100,0),'% Usage'))
Thanks, Angela
Best Answers
-
Don't JOIN Budget to Actuals. APPEND Budget to Actuals.
By Appending you'll avoid duplication and can avoid 'crazy math'.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"3 -
0
Answers
-
I may not be understanding your data correctly, but couldn't you just do SUM(`Budget`) for your denominator rather than doing a case statement for the 4 regions? Like this:
(CONCAT(ROUND((SUM(`ExpenseAmount`) / SUM(`Budget`)) *100,0),'% Usage'))
**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 -
Don't JOIN Budget to Actuals. APPEND Budget to Actuals.
By Appending you'll avoid duplication and can avoid 'crazy math'.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"3 -
0
-
Thanks @jaeW_at_Onyx ! The Append did the trick. At first I couldn't see the budgets bu then realized I needed to add a new date field that would pull in both budget lines and expense lines. I finally just cleaned up the last card and they look great!
Thanks again!
Angela
0 -
Thanks @MarkSnodgrass. That is definitely a cleaner statement :) but my issue went deeper to the actual dataset setup.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 618 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 741 Beast Mode
- 58 App Studio
- 40 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive