8 Replies Latest reply on Mar 11, 2013 12:50 PM by Armando Ramirez

Calculate AVG of an aggregation at a Higher level

I have # of Items on sale, at the Region-Store-ProductCategory level as shown in the workbook attached.

Then I would like to calculate the AVG of the # of Items at the Region-ProductCategory level and display those results as shown below:

Any thoughts??

Thanks!

• 1. Re: Calculate AVG of an aggregation at a Higher level

Armando--

I this can be solved by removing store from the view and then changing the aggregation of Items from Sum to Avg by right clicking on the Items pill on text, hovering over Measure, and the clicking Average.

If you want both the Sum and displayed, you could then just double click Items again to bring it into the table as well.

Hope this helps,

Dan

• 2. Re: Calculate AVG of an aggregation at a Higher level

Armando,

If you want to leave store in the view, you can do this using a table calculation, WINDOW_AVG.  Using a windowing table calculation, you can define the window for which the value is computed -- so you can get higher level aggregation even when including lower level detail in the view.  I've attached a workbook that demonstrates.  I'd be happy to answer any questions you might have!

Regards,

Joshua

• 3. Re: Calculate AVG of an aggregation at a Higher level

Armando,

Just a side-note.  I simplified the calculation in the workbook, but if performance is a concern and as long as you are using Tableau 7 (what follows is not needed in Tableau 8), then the second table calculation really should be this code:

IF FIRST() = 0

THEN WINDOW_AVG(SUM([Items]), 0, IF FIRST() = 0 THEN LAST() ELSE 0 END)

END

It evaluates the same, but does prevent Tableau's internal engine from performing multiple expensive sorts when only one is really needed.

• 4. Re: Calculate AVG of an aggregation at a Higher level

Actually, my real case is a little bit harder...

The # of items is given by the "=Number of records" metric as shown in the NEW workook attached.

So, how can I accomplish this?

In the workbook attached, the expected result should should looks like:

• 5. Re: Calculate AVG of an aggregation at a Higher level

Armando,

Are you looking for the average or the total number of items in a  region-category?  Because in your example, there are 5 items in R1, Cat1.  So the average (Number of Rercords / Number of Region-Category) is the same as the total number of items in each region-category.

If either gives the same result, then this code will give you the number per category:

WINDOW_SUM(SUM([Number of Records]))

Or is there another level of complexity that I'm not understanding?

Regards,

Joshua

• 6. Re: Calculate AVG of an aggregation at a Higher level

Sorry !!

My mistake

The actual number should be 2.5 instead of 5:

Which es: (3+2) / 2 = 2.5 ...this is what I call the AVG

And I dont want to have the STORES in the visualization..

Makes more sense now??

• 7. Re: Calculate AVG of an aggregation at a Higher level

Armando,

Thanks for the clarification!  The fact that you don't want stores in the view makes it much easier (and much more along the lines of Dan Huff's original suggestion).

In that case, it is a simple aggregate calculation:

SUM([Number of Records]) / COUNTD([Store])

I've attached the workbook so you can see the adjusted table calculation if you do want stores in the view and the much simpler aggregate calc if you don't.

Regards,

Joshua

• 8. Re: Calculate AVG of an aggregation at a Higher level

It worked!

Thanks