Hi Erling,

Have you looked at duplicating the measure by creating a calculated field called Average Order Quantity and defining this as equal to Order Quantity? You can then define the aggregation property of the new Average Order Quantity field to Average by right-clicking the measure, selecting Field Properties > Aggregation > Average.

You can use both these measures on the grid now to get two different aggregations of the same base measure. Would that help?

Cheers

Siraj

Hi Erling,

I have attached a sample workbook to illustrate this.

Is this what you need?

Cheers

Siraj

Nice question! I'd never thought about doing this before, but it actually works really well. Or at least I think this is what you want.

I presume in the Superstore Sales example you are looking for the average of the daily sales by category, at whatever levels of the date and product category hierarchy you are displaying?

The attached Superstore workbook example has three calculated fields:

• [Order Date (copy)] - this is on level of detail at the level of DAY() and is needed so that the daily figures are available for the averages, even if the [Order Date] on columns is rolled up to a more summary level.
• [Daily Average Order Quantity] - this is defined as WINDOW_AVG(SUM[Order Quantity])), with Compute Using set to [Order Date (copy)]. So this gives the average over the days in each partition of the total Order Quantity. The partitions are defined by the level of roll-up of the Order Date and Product Category hierarchies
• Is First? - this is just used to filter the view down to a single value per row and column - otherwise the values are shown (overlaid on top of each other) for all days in each partition (because of the DAY() on level of detail)

Is that what you were after?

Edit: Having posted it I thought I'd better just check if it actually works and it's not looking good.    I'll have another go...

Actually I think it is working "correctly" - just maybe not doing what you would expect. The issue is that the data is sparse - so there are not sales for every type of product on every day. So let's make it really simple and say we had just these 2 orders in January 2012.

Date | Category | Product | Quantity

1 Jan 2012 | Bookcases | Metal Bookcase | 31

2 Jan 2012 | Bookcases | Wooden Bookcase | 31

You might think that having sold 31 each of metal and wooden bookcases in January (31 days) the daily average orders for metal bookcases in January is 1, for wooden bookcases is 1 and overall for bookcases is 2 orders per day.

But that workbook I just posted would tell you that the daily average for metal bookcases is 31, for wooden bookcases is 31 and overall for all bookcases is also 31. The reason for that is that when broken down by Product there were 31 orders on each of two days and no other records, so the average is 31 orders per day for which there is any data.

It would give the answer you expect if there were zero records for every product for every day when there were no orders.

I've attached another version with a second sheet which I think now works as expected - but I haven't checked it thoroughly. Note that the date hierarchy is now driven off level of detail.

This relies on the way that Tableau pads missing dates and uses some table calculation tricks to turn the padded nulls into zeros for the computation (even though they still show as missing if you expand out the display). As I say, I think it now works, but I only barely understand it and I'm not sure. Definitely safer to pad your data at source if you want to try this approach.