Average of averages

[Anonymous]

Hello,

I'm trying to achieve something that shouldn't be that hard, but I can't figure it out.

I have a table of product reviews by version. I want to display in another table the average of the average per version by product.

My current measure:

Avg per version = CALCULATE(AVERAGEX(Products, Products[Review]), ALL(Products[product_version]))

(product_version is the concatenation of product and version)

When aggregated by product, it should give me 1.5 for A (average of 2 for v1.2 and 1 for v1.3), but here I have 1.75, the average of all rows of A.

PS : I need the measure to work as expected when rows are filtered by the bar chart

[snemilipuri]

I have been looking for this solution since a long time. Finally, you resolved it. Thanks a lot.

[v-deddai1-msft · Accepted solution]

Hi  @Anonymous ，

You can try to use the following measure:

Measure = var a = SUMMARIZE(Products,Products[Product],Products[Version],"averagebyproduct_version",AVERAGE(Products[Review])) return AVERAGEX(a,[averagebyproduct_version])

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Thank you, that works perfectly! It was not complicated after all...

[PaulDBrown]

@Anonymous 

Shouldn't the total average be 2.5 (and not 2.17)? (1.5 + 3.5 = 5; 5/2 = 2.5)

[Anonymous]

This solution fits my need because I need the total to be the average of all versions regardless the average per product.

[PaulDBrown]

@Anonymous 

I must have misunderstood your quest then. Btw, how are you getting the total average by month for the bar chart? Is it just the average by version (regardless of the product)?

[PaulDBrown]

@Anonymous 

Try:

Avg per version =
AVERAGEX (
    ALLEXCEPT ( Products, Products[Products], Products[Version] ),
    SUM ( Products[Review] )
)

[Anonymous]

@PaulDBrown  It doesn't work, it gives me an average of 14 for A and 7 for B.

[PaulDBrown]

@Anonymous 

Can you provide the data you posted above in a file (upload to Onedrive, Google Drive, Dropbox ...and share from there)?

[Anonymous]

https://drive.google.com/file/d/1S2lHRaTVCfRGMJCq9IZrzk5ePcUxoMB7/view?usp=sharing

[PaulDBrown]

@Anonymous 

Thank you for the sample. I've added a calendar table and dimension tables. These measures seems to work (you need to select them based on the filter context - see image.

Average by product =
AVERAGEX (
    SUMMARIZE (
        'Products',
        'Dim Product'[Product],
        'Dim Version'[Version],
        "_product",
            AVERAGEX (
                SUMMARIZE (
                    'Products',
                    'Calendar Table'[Date],
                    'Dim Product'[Product],
                    'Dim Version'[Version],
                    "Reviews", AVERAGE ( 'Products'[Review] )
                ),
                [Reviews]
            )
    ),
    [_product]
)

Average totals =
AVERAGEX (
    SUMMARIZE (
        'Products',
        'Dim Product'[Product],
        "_totals", [Average by product]
    ),
    [_totals]
)

I've attached the sample file