Monthly Fixed Average

[mdjoshua94]

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 example, it would be 51/(3 months).

Please find below link to PBIX https://drive.google.com/file/d/1Ww6bpw2RGuG6z7ygYqVUqsSoP084rxxN/view?usp=sharing 

Regards,
Amateur PBI user

[v-jingzhang · Accepted solution]

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.

[amitchandak]

@mdjoshua94 , Try something like this

calculate(AverageX(values(Date[Month]), [Total Sales]), allselected(Date))