DAX: Monthly AVG

0
votes

I am looking for the "best practice" when it comes to calculating Monthly Averages in DAX. Ideally, I would like to use some of the DAX built in Time Intelligence Functions.

I have a measure called "Total Units". Total Units = COUNT(Table[UnitId])

Each row in my table represents when a single unit was sold.

When I put my Total Units and Sales Date into a bar chart, I can see how many units were sold every month.

How do I now calculate the monthly average?

Month         Total Units (Sold)
Jan 2019      10
Feb 2019      30

I want a measure that will tell me that the Monthly AVG is (10+30)/2 = 20

2
powerbidax

2 Answers

0
votes

There are a couple of ways to solve this. Assuming you have a [Month] column on your table, you could simply do:

MonthlyAvg = DIVIDE ( [Total Units] , DISTINCTCOUNT( 'Table'[Month] ) )

In other words, simply dividing the total with the number of months considered.

If you have a more "proper" data model, with a separate calendar dimension, you should do something like:

MonthlyAvg = AVERAGEX( VALUES( 'Calendar'[Month] ) , [Total Units] )
0
votes

All you need is a simple average measure as below,

Total Avg = AVERAGE(Total Units (Sold))

When you select any period for date, measure will adjust and provide the average for the selection.

Let's say you select for year 2019 and Months are Jan, Feb, March

(Jan + Feb + March)/3

Hope it helps!!