Percentage of values in a column
Hello,
In the beast mode how should I get the percentage of column totals like below, 50 hours worked is the 12 percent of the total hours (410). I need to create a column like % Hours so that I can make further calculated fields based upon that.
Provider | Hours worked | Total Tickets | % Hours | % Tickets |
---|---|---|---|---|
John | 50 | 20 | 0.12 | 0.10 |
David | 60 | 30 | 0.15 | 0.15 |
Mark | 100 | 50 | 0.24 | 0.25 |
Antony | 200 | 100 | 0.49 | 0.50 |
Best Answer
-
@agolla2 you can use a window function to achieve this.
SUM(SUM(value)) over ()
Then you can use that as an input in another beast mode (you could also combine these all into 1 beast mode). Best to separate it out if you will be using it in other calculations.
SUM(value)/total_hours
Which can then be used to generate your desired output.
A couple things to keep in mind. This window function calculates the total over all data. If you need to split by group, you'll need to make use of the PARTITION BY argument.
You can use the same approach to calculate % of tickets.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**1
Answers
-
@agolla2 you can use a window function to achieve this.
SUM(SUM(value)) over ()
Then you can use that as an input in another beast mode (you could also combine these all into 1 beast mode). Best to separate it out if you will be using it in other calculations.
SUM(value)/total_hours
Which can then be used to generate your desired output.
A couple things to keep in mind. This window function calculates the total over all data. If you need to split by group, you'll need to make use of the PARTITION BY argument.
You can use the same approach to calculate % of tickets.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**1 -
Hey David! Thanks for sharing it worked for me.
But I'm trying to use these beast mode calculations to do a weighted average like we do in excel.
Example of single value weighted average for 2 columns: "=SUMPRODUCT(F48:F70,E48:E70)/SUM(E48:E70)"
My question is can we still try to do the weighted average even after using the window function calculation we did. Because we might face an aggregation issue as in, we cannot refer an already referred beast mode calculation. I hope you got my question.0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 303 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 104 SQL DataFlows
- 637 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 763 Beast Mode
- 65 App Studio
- 42 Variables
- 709 Automate
- 184 Apps
- 460 APIs & Domo Developer
- 54 Workflows
- 11 DomoAI
- 39 Predict
- 16 Jupyter Workspaces
- 23 R & Python Tiles
- 401 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 8 Software Integrations
- 134 Manage
- 131 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive