How to display bar totals correctly
Sorry for the ambiguous title, but I'm not sure what words to use to explain this in a summarized way.
I have a dataset that captures financials for projects. Each project can have up to 9 financial categories, and financials are captured per month. Therefore, the dataset includes a row for every single category/month combination. Even a 3 month project can have up to 36 rows for financials if it utilized all 9 categories. The exact costs in the dataset are in a column for PlannedCost or ForecastCost
I have a card for a bar chart where I'm showing whether a project is under or over budget. This compares the PlannedCost to the ForecastCost via BeastMode. In total, there are 106 projects that meet my criteria at time of writing, so this is what the two bars should add up to, but the combined total of the Under Budget & Over Budget bar columns is 6406. This is because it's counting every single row from the dataset.
How can I get this chart to be based on the unique project count, i.e. on the total for all months/categories, rather than it counting every individual row?
Thanks!
Answers
-
Hi @PJG
Anyway you can share a screenshot of the entire analyzer for us to see? It will be easier to understand
John Le
You're only one dashboard away.
Click here for more video solutions:
0 -
Hi @DashboardDude Sorry for taking a month+ to get back to you - been pulled in many different directions since. Is this useful at all?
My On-Budget code is:
case
when ForecastCost < PlannedCost * 1.01 AND ForecastCost - PlannedCost > 20000 then 'Over Budget'
when ForecastCost > PlannedCost * 1.01 AND ForecastCost - PlannedCost> 5000 then 'Over Budget'
else 'Under Budget'
EndAgain, the problem seems to be that it's comparing every single Month/Cost Category combination row from the dataset when I just need it to compare the total per project. Maybe the question is simply how to I SUM all the PlannedCost and ForecastCost per project before performing the budget calculation?
Thanks!
0 -
Hi @PJG ,
I think I understand but still a little vague. I think I would do a count distinct in the ETL that you can then use to do your sum or count.
John Le
You're only one dashboard away.
Click here for more video solutions:
0
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
- 600 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 685 Beast Mode
- 43 App Studio
- 38 Variables
- 655 Automate
- 170 Apps
- 438 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 383 Distribute
- 110 Domo Everywhere
- 267 Scheduled Reports
- 6 Software Integrations
- 111 Manage
- 108 Governance & Security
- 8 Domo University
- 25 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive