Stuck calculating monthly averages

[rogerdea]

Hi again

I am trying to have a chart which shows average monthly values but unable to get the average values working.  I have tried some dax from other posts on this forum but they haven't helped me unfortunatley. 

My approach has been to calculate the number of values by month, and then calculate an average using these monthly values.

For the monthly values i did a calculated column as the following however what this did is give me values which are not the average.  (eg  January shows 1250 in the image below)

Monthly Totals = CALCULATE(SUM('LB Incident'[Rec Count]),ALLEXCEPT('LB Incident','LB Incident'[Month]))

The average monthly launches are showing the same value as the "Rec Count", so either this calculation is wrong or its a a result of the first calculation:

Average Monthly Launches = AVERAGEX(VALUES('LB Incident'[Month]),[Record Count])

You will see i have two ways of counting numbers of records. [Rec Count] which is a calulated column and [Record Count] which is a measure.  This was because for the monthly totals i had to use a column to reference.  At this point was getting a bit lost, hence here!

[dax · Accepted solution]

Hi rogerdea,

I am not clear about your requirement, did you want to get result like below?

If so , you could try to use below measure 

avg month =
CALCULATE (
    AVERAGE ( 'LB Incident'[Rec count] ),
    FILTER (
        ALL ( 'LB Incident' ),
        'LB Incident'[Month] = MIN ( 'LB Incident'[Month] )
    )
)

Best Regards,

Zoe Zhi

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

[rogerdea]

Thanks so much Zoe, thats exactly what i want.  I have not got it working yet though, my averages are all "1", but this may be because of the field I am using in the calculation, so will have look today and see if it works.  I'll let you know!

[dax]

Hi rogerdea,

Did this help you solve your issue? If so and if you'd like to, you could mark corresponding post as answer or share your solutions. That way, people who in this forum and have similar issue will benefit from it.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

[rogerdea]

The solution work perfectly with my test data so have accepted it.  It's just i can't get my real life case working.  I think it's a data issue though so hope to figure it out soon!