Average question
I am trying to find out the average Orders that my team does every week.
Some days we work 5 days, some 6, some 7.
So I want to find for week 6 of the year  What was my average Orders?
Orders done on (2/5 + 2/6 +2/7 + 2/8 +2/9) / 5
How can i create a beast mode for this?
Best Answer

@shreeyab if we zoom on only the row you highlighted, if I'm understanding properly, and assuming your
Orders
number is right (I just find it odd it has decimals), are you expecting to see the following?If this is the case, then you'll need to use the FIXED function, so that each day can look at more than the values for it, depending on whether your card will always be limited to a single week or not, the formula will require a BY clause or not (it requires it if you might end up showing multiple weeks in the same card). If a single week is shown (as filtered by the date range), something like this should work.
SUM(SUM(`Orders`) FIXED(BY `AssignedTo`))/AVG(COUNT(DISTINCT `ProductionScheduleDate`) FIXED(BY `AssignedTo`))
If it can show multiple weeks and you need the weekly average:
SUM(SUM(`Orders`) FIXED(BY `AssignedTo`, `ProductionScheduleDate`  WEEKDAY(`ProductionScheduleDate`)))/AVG(COUNT(DISTINCT `ProductionScheduleDate`) FIXED(BY `AssignedTo`, `ProductionScheduleDate`  WEEKDAY(`ProductionScheduleDate`)))
Whether the denominator needs to have the BY clause on the
AssignedTo
depends on whether for example in the case ofDallas
in your example it should be divided by 4 or 5. Here's an example using the second formula which is more flexible.0
Answers

How about this?
COUNT(`Orders`) / (CASE WHEN DAYOFWEEK(`Order Date`) = 2 THEN 5
WHEN DAYOFWEEK(`Order Date`) = 3 THEN 6 ELSE 7 END)
The CASE statement checks the day of the week of each order date:If the order date falls on Monday (DAYOFWEEK(Order Date) = 2), it means your team worked for 5 days in that week. This is because Monday is typically the start of the workweek, and we assume the team works Monday through Friday.
If the order date falls on Tuesday (DAYOFWEEK(Order Date) = 3), it means your team worked for 6 days in that week. This is because if the first day of the week is a Tuesday, it implies that the team works Tuesday through Sunday.
For all other days (Wednesday to Sunday), the ELSE part of the CASE statement assigns 7 days to the week. This assumes a standard workweek from Monday to Sunday.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 
@MichelleH  i tried your logic, but it didnt work the way i wanted.
Here is the ideal scenario 
#this is a sample.
work can be 567 days0 
@MichelleH yup, i tried that, but still wasnt getting the right answer
0 
Its hsould show the same average for that week  if i change the timeline to This year  by Week  it should show the sum and avg by week
0 
@shreeyab The issue is because you are grouping your data by Day, so your calculation only includes orders from that particular day. Just so I understand your requirements, what should a weekly average by day represent? Is this the week to date average, rolling 7 day average, or overall average (including future dates)?
1 
overall week average @MichelleH
0 
@shreeyab if we zoom on only the row you highlighted, if I'm understanding properly, and assuming your
Orders
number is right (I just find it odd it has decimals), are you expecting to see the following?If this is the case, then you'll need to use the FIXED function, so that each day can look at more than the values for it, depending on whether your card will always be limited to a single week or not, the formula will require a BY clause or not (it requires it if you might end up showing multiple weeks in the same card). If a single week is shown (as filtered by the date range), something like this should work.
SUM(SUM(`Orders`) FIXED(BY `AssignedTo`))/AVG(COUNT(DISTINCT `ProductionScheduleDate`) FIXED(BY `AssignedTo`))
If it can show multiple weeks and you need the weekly average:
SUM(SUM(`Orders`) FIXED(BY `AssignedTo`, `ProductionScheduleDate`  WEEKDAY(`ProductionScheduleDate`)))/AVG(COUNT(DISTINCT `ProductionScheduleDate`) FIXED(BY `AssignedTo`, `ProductionScheduleDate`  WEEKDAY(`ProductionScheduleDate`)))
Whether the denominator needs to have the BY clause on the
AssignedTo
depends on whether for example in the case ofDallas
in your example it should be divided by 4 or 5. Here's an example using the second formula which is more flexible.0 
@marcel_luthi  Its not exactly orders  but its a different metric. I used Orders to make it easier to understand.
Your image is exactly what I am looking for!
Based on your explanation  I will have to take the second approach and try to see if that works.
0 
@marcel_luthi  Thank you again, it worked.!
1 
@marcel_luthi i have to update this logic and im not sure how.
0 
SUM(SUM(`Orders`) FIXED(BY `AssignedTo`, `ProductionScheduleDate`  WEEKDAY(`ProductionScheduleDate`)))/AVG(COUNT(DISTINCT `ProductionScheduleDate`) FIXED(BY `AssignedTo`, `ProductionScheduleDate`  WEEKDAY(`ProductionScheduleDate`)))
This solution worked but business now wants to change this to show average of 5 days and exclude holidays0
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
 602 Datasets
 2.1K Magic ETL
 3.7K Visualize
 2.4K Charting
 693 Beast Mode
 43 App Studio
 39 Variables
 658 Automate
 170 Apps
 441 APIs & Domo Developer
 42 Workflows
 5 DomoAI
 32 Predict
 12 Jupyter Workspaces
 20 R & Python Tiles
 386 Distribute
 111 Domo Everywhere
 269 Scheduled Reports
 6 Software Integrations
 113 Manage
 110 Governance & Security
 8 Domo University
 30 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 98 Community Announcements
 Domo Community Gallery
 4.8K Archive