Calculate Monthly Average and sum multiple months average

[Anonymous]

Hi Team, I have a requirement to average the monthly sales and sum the monthly averages when we select multiple months.

I have a Salestable as below. If I filter for Product = "Watch" and YearMonth = "Jan-2022" then I want to see the total average of Watch for the month of Jan 2022 (i.e., 999). - Which is working fine. But

If I filter for Product = "Watch" and selected multiple months or multiple years like YearMonth = "Jan-2022" and "Feb-2022" then I want to see the total sum of both months average (i.e., Jan-2022 = 999 and Feb-2022 = 894 so my result should be 999+894 =1893).

SalesTable:

Product	SaleDate	Yearmonth	Price
Watch	1-Jan-22	Jan-2022	999
Watch	10-Jan-22	Jan-2022	999
Watch	1-Feb-22	Feb-2022	894
Watch	15-Feb-22	'Feb-2022	894
Watch	1-Apr-22	Apr-2022	434
Pen	1-Mar-22	Mar-2022	32
Pen	14-Mar-22	Mar-2022	35
Pen	9-Apr-22	Apr-2022	22
Table	1-Jan-22	Jan-2022	999
Table	10-Jan-22	Jan-2022	999

I need this to be implemented in a measure. Can someone please help me with the same?

Thanks!!

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous;

You could create a measure.

Measure = SUMX(SUMMARIZE('Table',[Product],[Yearmonth],[Price]),[Price])

 The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

Sum Average = 
var summaryTable = ADDCOLUMNS( SUMMARIZE('Table', 'Table'[Product], 'Table'[Yearmonth] ),
    "@avg", CALCULATE( AVERAGE('Table'[Price]))
)
return SUMX( summaryTable, [@avg] )

[Anonymous]

@johnt75 - Thanks for your swift response! I had applied your logic, but I'm getting a big number when I filter for multiple months

[Anonymous]

CALCULATE (
    SUMX (
        SUMMARIZE (
            TABLE,
            TABLE[PRODUCT],
            TABLE[YearMonth],
            "@AVG", FIRSTNONBLANK( TABLE[PRICE],0 )
        ),
        [@AVG]
    )
)

I wrote this logic and it is working as expected. however the totals are showing 0. Can you please help me here @johnt75 ?

[johnt75]

Not sure what you're trying to get with FIRSTNONBLANK. The measure I posted did work with multiple months selected