# Average of average caculation

**URL:** <https://community.amazonquick.com/t/average-of-average-caculation/19622>\
**Category:** Q&A\
**Tags:** calculations, Business-Intelligence-Engineer, quick-sight\
**Created:** [October 16, 2023, 2:59pm UTC](https://community.amazonquick.com/t/average-of-average-caculation/19622 "2023-10-16T14:59:55Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Iris\_Zhou](https://avatars.discourse-cdn.com/v4/letter/i/8dc957/32.png) [@Iris\_Zhou](https://community.amazonquick.com/u/Iris_Zhou)\
**Post date:** [October 16, 2023, 2:59pm UTC](https://community.amazonquick.com/t/average-of-average-caculation/19622/1 "2023-10-16T14:59:56Z")

</div>

| | week1 | | | | | | |
| --- | --- | --- | --- | --- | --- | --- | --- |
| | c | b | c | s | d | f | g |
| OTD % - offers average | 0.0% | 11.1% | 75.0% | 0.0% | 65.6% | 0.0% | 100.0% |
| OTD % - partners average | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% |
| otd\_num | 0 | 2 | 24 | 0 | 227 | 0 | 1 |
| otp denom | 56 | 18 | 32 | 3 | 346 | 17 | 1 |

Right now, i can’t get the right formula for [OTD % - partners average] , please help.

The logic for calculation is sum(otd\* OTD % - offers average)/ counta( vendor alias)  
ex: OTD % - partners average =0\*56 +11.1%\*18+75% \* 32+0 \* 3+65.6% \* 346+ 0 \* 17+ 100% \*1= 36.28%

otd\_num formula: distinct\_count(ifelse({otd\_status}=1,{Offer Published},NULL))  
otp denom formula :distinct\_count({Offer Published})  
OTD % - offers average= otd\_num/otp\_donom

---

<div class="post-metadata">

**Author:** ![ErikG](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/erikg/32/14524_2.png) [@ErikG](https://community.amazonquick.com/u/ErikG)\
**Post date:** [October 17, 2023, 7:46am UTC](https://community.amazonquick.com/t/average-of-average-caculation/19622/2 "2023-10-17T07:46:31Z")

</div>

Hi @Iris_Zhou ,  
did you try to break the calculation down to it parts?

1. calculate the “otd %” `otd* OTD % - offers average` for each
2. the sumOver(otd %,week)
3. final 2. / counta( vendor alias)  
BG