# Average question

Member

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?

• Coach

@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 of `Dallas` in your example it should be divided by 4 or 5. Here's an example using the second formula which is more flexible.

• Coach

@shreeyab This largely depends on how your data is structured, but assuming you have one row per order you can use a beast mode like this and group by week:

```count(`Order`)/count(distinct `Order Date`)
```

• Coach

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.

** Did this solve your problem? Accept it as a solution! **

• Member

@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 5-6-7- days

• Coach

@shreeyab If your data is already grouped by day, then you can change the numerator to `SUM(`Orders`)`

• Member

@MichelleH yup, i tried that, but still wasnt getting the right answer

• Coach

@shreeyab Can you please show some screenshots or examples of the incorrect results?

• Member

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

• Coach

@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)?

• Member

overall week average @MichelleH

• Coach

@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 of `Dallas` in your example it should be divided by 4 or 5. Here's an example using the second formula which is more flexible.

• Member
edited March 6

@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.

• Member
edited March 6

@marcel_luthi - Thank you again, it worked.!

• Member

@marcel_luthi i have to update this logic and im not sure how.

• Member
```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 holidays