Field Average in Table Card excluding zeros?
Good Morning,
I use Salesforce to connect to Domo. I have fields showing Total Leads Completed vs Total Leads. Let's say I have 10 records in Salesforce where only 1 of those have 7 leads completed out of 10. A formula calculates this as 70% in beastmode.
Here's the problem, when setting the label to "Average", it doesn't show 70%, it shows 7% because it's including the average of 0& for the other 9 records.
How can I get the score to show 70% ?
If I set to Max or Minimum it would show the max score or lowest score out of the 10...so that won't work either.
Best Answer
-
The Average function will ignore rows where the value being averaged is NULL. Because of this, you can switch your zeros to nulls and the average function should behave how you're expecting.
Do accomplish this, you could try something like this:
AVG(
CASE
WHEN (`Total Leads Completed`/`Total Leads`) > 0 THEN (`Total Leads Completed`/`Total Leads`)
END
)
That will give you the average of any records where the completed/total is greater than 0. You may need to adjust your fields/formula, of course, but hopefully this gives you a good start.
I hope that helps!
1
Answers
-
The Average function will ignore rows where the value being averaged is NULL. Because of this, you can switch your zeros to nulls and the average function should behave how you're expecting.
Do accomplish this, you could try something like this:
AVG(
CASE
WHEN (`Total Leads Completed`/`Total Leads`) > 0 THEN (`Total Leads Completed`/`Total Leads`)
END
)
That will give you the average of any records where the completed/total is greater than 0. You may need to adjust your fields/formula, of course, but hopefully this gives you a good start.
I hope that helps!
1
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