Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Monthly Total - Annual Aaverage

Hi all

 

I'm trying to do a monthly total - annual average measure for my latest reporting project.

 

The idea being that this will be the first step towards a least squares comparison for the current year's data against previous data.

 

I've made ( for ease) the measures:

total = COUNTA(ApplicationsPBI[GUID])

MonthMean = CALCULATE(COUNT(ApplicationsPBI[GUID]),ALL(ApplicationsPBI[Application Date]))

 

I have data with dates over a number of years.

 

What I am looking for is a each month's count - the annual average.

 

When I put any measure in a graph it just calculates the monthly avreage and not the annual average.

 

I can not seem to get the filters off the measure! It feels like I'm missing something obvious. Can anyone help?

 

Thanks

Beth

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi

     

    Spoiler
    Spoiler! ;)

    Thanks. I did eventually figure this out! Go me!

     

    Acad Mean = CALCULATE(COUNTA(ApplicationsPBI[GUID]),ALLEXCEPT(ApplicationsPBI,'Date Order (Applications)'[Acad Period]))/12

     

     

    It was just a case of figuring out how to remove the right filter, in this case all but acad period. So now when I use this measure, it gives me the monthly average for the acad period regardless of the number of appliocations in any given month.

     

    Not sure that makes sense without context though!

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Anonymous,

    In order to provide your appropriate DAX, please share dummy data of your table and post expected result based on the dummy data.

    Regards,
    Lydia

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi

       

      Spoiler
      Spoiler! ;)

      Thanks. I did eventually figure this out! Go me!

       

      Acad Mean = CALCULATE(COUNTA(ApplicationsPBI[GUID]),ALLEXCEPT(ApplicationsPBI,'Date Order (Applications)'[Acad Period]))/12

       

       

      It was just a case of figuring out how to remove the right filter, in this case all but acad period. So now when I use this measure, it gives me the monthly average for the acad period regardless of the number of appliocations in any given month.

       

      Not sure that makes sense without context though!