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 YearMon...
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.

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 ?

     

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      4 years ago

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