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.

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