7 Replies Latest reply on May 22, 2019 6:03 AM by Zhouyi Zhang

# Average of averages of averages.

Hello all,

I have the following hierarchy of groups: stores, dealers, brands, market. Each dealer has a number of stores. Each brand has a number of dealers. Each market has a number of brands.

I have one score for each store. I need to calculate a score according to the rule:

The score for each dealer is the average of the scores of the dealer's stores.

The score for the brand is the the average of the dealers' scores

The score of the market is the average of the brands' scores.

Is there a way to calculate all these scores in a workbook?

PS I cannot attach a workbook cause the material is confidential but, if needed, I can create a fake dataset.

• ###### 1. Re: Average of averages of averages.

Hi Othon

Please attach a workbook in .tbwx format based on a 'dummy-dataset'

Packaged workbooks: when, why, how |Tableau Community Forums

Thanks,

Norbert

• ###### 2. Re: Average of averages of averages.

Attached you will find a workbook.

• ###### 3. Re: Average of averages of averages.

Hi Othon,

Please attach once more in .tbwx format (data attached)

Thanks,

Norbert

• ###### 4. Re: Average of averages of averages.

Sorry,   I am new to the forum.. The correct file is now attached..

• ###### 5. Re: Average of averages of averages.

Hi Othon,

Yes you can.

I can think of one easy data source format using which you can do this:

Market    Brand    Dealer    Store    Score

1              A            X            a            5

1              A            Y            b            4

1              A            Y            c            5

1              B            Z            d            4

...

Market, Brand, Dealer & Store are dimensions. Score is a measure.

If you drag Market into the sheet and right click drag score into the sheet and choose AVG as aggregation. This will compute the average of all scores in a respective market and show the results.

If you need averages of averages, then you can use calculated fields to write LoD expressions.

Dealer Score = { FIXED [Dealer] : AVG([Score]) }

Brand Score = {FIXED [Brand] : AVG({ INCLUDE [DEALER] : AVG([Score]) }) } etc.

I've attached a workbook with Superstore data source. I've used Categories and Subcategories as two dimensions and Sales as the measure. Refer to 'Using LoD' sheet. Avg of sales would be shown for Sub categories. Avg. of Sub category sales is shown for Categories. You can see that the totals computed for categories using AVG in 'Using Totals' sheet matches the average of average figures for Categories in 'Using LoD' sheet.

Hope this helps!

• ###### 6. Re: Average of averages of averages.

Hi Othon,

Find my approach as reference below and stored in attached workbook version 10.5 located in your original post here:

Average of averages of averages.

1. 01 M avg Dealer Score: {fixed [dealer]:avg([score])}

2. 02 M avg Brand Score : {fixed [brand]:avg([score])}

3. 03 M avg Market Score: {fixed :avg([score])}

4. LOD functionality in Tableau is explained here:

Thanks,

Norbert

• ###### 7. Re: Average of averages of averages.

Hi, Othon