Forum Discussion

mdjoshua94's avatar
mdjoshua94
Frequent Visitor
5 years ago
Solved

Monthly Fixed Average

Dear all, How do I calculate the avarage monthly sale in a year? I want to report the values in a column chart as Total Sum/12 so that I can filter against the product type. For the above...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi mdjoshua94 

     

    You could add a month column and use this column in visuals. Then create a measure like below.

    Average = 
VAR total = CALCULATE(SUM('Table'[Total Sale]),ALLEXCEPT('Table','Table'[Product]))
VAR months = CALCULATE(DISTINCTCOUNT('Table'[Month]),ALLEXCEPT('Table','Table'[Product]))
RETURN
DIVIDE(total,months)

    Is this what you want?

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
5 years ago

Hi mdjoshua94 

 

You could add a month column and use this column in visuals. Then create a measure like below.

Average = 
VAR total = CALCULATE(SUM('Table'[Total Sale]),ALLEXCEPT('Table','Table'[Product]))
VAR months = CALCULATE(DISTINCTCOUNT('Table'[Month]),ALLEXCEPT('Table','Table'[Product]))
RETURN
DIVIDE(total,months)

Is this what you want?

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.