Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate Monthly Average and sum multiple months average

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:

ProductSaleDateYearmonthPrice
Watch1-Jan-22Jan-2022999
Watch10-Jan-22Jan-2022999
Watch1-Feb-22Feb-2022894
Watch15-Feb-22'Feb-2022894
Watch1-Apr-22Apr-2022434
Pen1-Mar-22Mar-202232
Pen14-Mar-22Mar-202235
Pen9-Apr-22Apr-202222
Table1-Jan-22Jan-2022999
Table10-Jan-22Jan-2022999

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

 

Thanks!!

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

5 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago
    Sum Average = 
var summaryTable = ADDCOLUMNS( SUMMARIZE('Table', 'Table'[Product], 'Table'[Yearmonth] ),
    "@avg", CALCULATE( AVERAGE('Table'[Price]))
)
return SUMX( summaryTable, [@avg] )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago
        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 ?

         

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.