Average of averages

[Bobbys]

Hi everyone.

I'm struggling with making my Matrix visual show the average of the column. 

Note that the values for each row are averages as well. This is the DAX I've used: 

COGS endring1 = AVERAGEX(VALUES('calendar'[Time period sort]), [COGS previous period])
The total should be (0 + 0 + 0,39 + 0,17) / 4 = 0,14

Any help would be well appreciated

[Bobbys]

Anyone?

[Anonymous · Accepted solution]

Hi @Bobbys ,

COGS_ending1 =
VAR _COGS_ending1 =
    AVERAGEX ( VALUES ( 'calendar'[Time period sort] ), [COGS previous period] )
VAR _SUMMAIRZE =
    SUMMARIZE (
        'Table',
        [Product],
        "COGSendring1", AVERAGEX ( VALUES ( 'calendar'[Time period sort] ), [COGS previous period] )
    )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Product] ),
        _COGS_ending1,
        AVERAGEX ( _SUMMAIRZE, [COGSendring1] )
    )

In my sample, the [COGS previous period] part equal to average(table[]value]). Result is as below.

Best Regards,
Rico Zhou

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

[Bobbys]

Thanks for the response @amitchandak. 
I've tried that already. The problem with that is that my totals dissapear when using Values(Table[Product]). 

No idea why... 

[amitchandak]

@Bobbys , if the product is above the time then it needs to like

COGS endring1 = AVERAGEX(VALUES('Product'[Product]), [COGS previous period])

till product level follow aggregation of measure - COGS previous period

post that avg of products