Calculate Average of measure over time

[Anonymous]

Hey fellow Power BI user,

I have the following problem and cannot figure it out.

I have a measure in which I calculated the number of open jobs per month:  "OpenJobsPerMonth".

When I plot this against DateTime I get the following graph (now in table form):

Month  OpenJobsPerMonth

January         15

February       13   

March           0

April             12

May              10     

What I now want, is to calculate another measure with the average value independent of time.

I tried using the following DAX formula, but it gives me incorrect results, because it just gives me the same values when I plot it over time.

AverageofaMeasure =
AVERAGEX ( VALUES ( DateTimeTable[Month] ), [OpenJobsPerMonth] )

What I want to get is just one number replicated for every time moment, as in the "WhatIWant" column.

Please help me to write a formula to get this.

Month  OpenJobsPerMonth AverageofaMeasure WhatIWant

January         15                            15                           10

February       13                            13                           10

March           0                               0                            10

April             13                             13                           10

May              9                               9                            10

[v-eachen-msft]

Hi @Anonymous ,

You need to modify your measure.

AverageofaMeasure =
AVERAGEX ( ALL ( DateTimeTable[Month] ), [OpenJobsPerMonth] )

Here is the visual.

Best Regards,

Eads

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Thanks for your response, but I found another solution.

Below is the DAX formula with which I managed to get the results I needed:

AverageOpenJobs = 
CALCULATE([OpenJobs] / COUNTX(DISTINCT(DateTimeTable[Month]);DateTimeTable[Month]);ALL(DateTimeTable))