# How can I dynamically average over pivots

**URL:** <https://community.omni.co/t/how-can-i-dynamically-average-over-pivots/162>\
**Category:** Modeling\
**Created:** [October 10, 2024, 10:06am UTC](https://community.omni.co/t/how-can-i-dynamically-average-over-pivots/162 "2024-10-10T10:06:36Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![conor](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.omni.co/conor/32/45_2.png) [@conor](https://community.omni.co/u/conor)\
**Post date:** [October 10, 2024, 10:06am UTC](https://community.omni.co/t/how-can-i-dynamically-average-over-pivots/162/1 "2024-10-10T10:06:36Z")

</div>

Often you might want to pivot by a timeframe and get an average on the measure over the groupings within the timeframes. For example you might want to get the weekly active users for the last 4 weeks. Then average this value on a weekly basis. Here’s a workflow to do that in Omni so that the average updates when you change the filter to different numbers of weeks.

### Steps

1. Select your dimension, measure and pivot on a timeframe/dimension of your choice. Here I’ve got `order status`, measure is `order items count` and I’ve pivoted on weeks.
2. Add a new column and `move it outside the pivot`. Then insert the following into the calculation outside of the pivot `=AVERAGE(PIVOTOFFSET(C1, 0, 0, 1, 5000))`. Updating the `C1` reference to the column you want to average over in your pivot. This will take the average up to 5000 cols in your pivot.  
 ![Screenshot 2024-10-10 at 11.04.20](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/b7984051111b3a4eb0f5d0958dba401a79c55324.png)
3. Add your filter and see how the calculation updates when you change the values within the filter (see Loom).

https://www.loom.com/embed/bac12fd29499447c975f049eb9e3a1cd