cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

Regards,
Amateur PBI user

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

@mdjoshua94 , Try something like this

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.