5 Replies Latest reply on Jan 10, 2013 11:51 AM by Mark Holtz

# Average vs. window_avg

Hello,

I have a formula where I'm using window_avg with sum over the last two year where I get a different results than when I use 'Average' in my Excel worksheet.  The sum of the last 12 months in Tableau is the same as in Excel:  12,038,274.

For instance Project manager completed 12,038,274 in revenue in 2012, I want to know what is the average revenue year-to-date.  When I calculate the average revenue in Excel:  12,037,274/12 = 1,003,189.

Using the Window_Avg formula the result is 969,651.

The formula is made of two parts:  IF Year([Fiscal Month])=Year([Fiscal Month Filter]) and Month ([Fiscal Month])<=Month([Fiscal Month Filter]) then [Actual Equip \$ Sum] End

IF First ()=0 THEN Window_Avg(Sum(YTD Actual Equip \$ Sum]),0,IIF(First()=0,Last(),0)) End.

Can you explain why I'm getting different results using window_avg vs Average?

• ###### 1. Re: Average vs. window_avg

Hi Elizabeth,

To know precisely how your table calculation is calculating, we must know how you table/view is structured. What dimensions are included on the view with the WINDOW_AVG calculation?

Can you share a packaged workbook, or at the very least, a sample set of your data?

• ###### 2. Re: Average vs. window_avg

Hi Mark,  I uploaded a sample copy of my packaged workbook.

Rows:  Measure names

Text: Measure Value

Level of Detail:  Fiscal Month

Measure Values: YTD Actual Equip \$ Avg

Prev YTD Actual Equip \$ Avg

Filters: Measures Names

MY(Fiscal Month Filter)

The results should be on the worksheet labeled "Prev Year and YTD".

• ###### 3. Re: Average vs. window_avg

That's quite a workbook! But I'm not seeing data for 2012 that you referenced in your example. Is that not in here?

In the version you shared, if you select Feb-2011, then "Prev Year..." calculates for the full 12 months: 8,880,572 / 12 = 740,048. "YTD Actual..." calculates avg of Jan-2011 and Feb-2011 only: \$2,972,116 / 2 = 1,486,058.

Is this not the desired behavior? I arrive at both numbers averaging the monthly figures in Excel as well...I'm afraid I still don't understand what discrepancy you're seeing. Sorry-I'm not usually this daft...

• ###### 4. Re: Average vs. window_avg

After looking further into my dashboard I discovered the data didn't update.  The Window_Avg is working fine.

• ###### 5. Re: Average vs. window_avg

Ha!  It happens. Cheers.