4 Replies Latest reply on Feb 23, 2015 7:20 AM by Amber Smart

# Help with averaging variance calc

Hey TUG!

I am stuck and I am wondering if anyone out there has encountered this scenario and can walk me off the ledge

I have attached a workbook that has over-simplified dummy data (but you get the idea) where I am looking at year over year variances by Region, but I am wanting to see what the average regional variance is by week (not the overall variance per week).

Anyone in the group have any margin to take a looksie and see if you can shed some light on what I am missing? I will be forever grateful and those around me will be, too. Ha!

Thank YOU!

Message was edited by: Amber Smart

• ###### 1. Re: Help with averaging variance calc

Try this on your variance average calculation. I was able to get the results you wanted.

WINDOW_AVG(SUM([2015 Value])/SUM([2014 Value]))/COUNTD(Region)

1 of 1 people found this helpful
• ###### 2. Re: Help with averaging variance calc

Thank you, Guillermo! I struggled to make that work in my actual workbook for some reason... I ended up going the route of adding the Region to the row shelf, then hiding all but 1 region, then I hid the region header. Appreciate you taking a look at it! Thank you Thank you!

• ###### 3. Re: Help with averaging variance calc

Instead of using the COUNTD(Region) to keep it dynamic, set that value manually. So for your example you can do this: WINDOW_AVG(SUM([2015 Value])/SUM([2014 Value]))/2 where 2 is the number of regions in your viz. Or set that 2 to be a parameter to be customizable if needed in case the number of regions change.

• ###### 4. Re: Help with averaging variance calc

For this scenario I think I prefer it to be dynamic. But for other scenarios what you suggest could be a great solution. Thanks for the feedback! Appreciate it.