Forum Discussion

DaleWatkins's avatar
DaleWatkins
Regular Visitor
5 years ago
Solved

Average by row count per month

Hi,    Apologies if this makes no sense, 3rd time writing this...    DAX newbie here trying to work out how to calculate an average of row count for 1 month over an x amount of years, i.e January...
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    5 years ago

    Hi DaleWatkins ,

     

    Sorry for that we are not clear about your issue.

    Do you want to calculate the average number of rows in the same month in three years?

    If yes, you can refer the following measure.

     

    Measure = 
var _count = COUNTROWS('Table')
var _count_year = DISTINCTCOUNT('Table'[Year])
return
DIVIDE(_count,_count_year)

     

     

    If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

    It will be helpful if you can show us the exact expected result based on the tables.

     

    Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

     

    Best regards,

     

    Community Support Team _ zhenbw

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

v-zhenbw-msft's avatar
v-zhenbw-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi DaleWatkins ,

 

Sorry for that we are not clear about your issue.

Do you want to calculate the average number of rows in the same month in three years?

If yes, you can refer the following measure.

 

Measure = 
var _count = COUNTROWS('Table')
var _count_year = DISTINCTCOUNT('Table'[Year])
return
DIVIDE(_count,_count_year)

 

 

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

It will be helpful if you can show us the exact expected result based on the tables.

 

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

 

Best regards,

 

Community Support Team _ zhenbw

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

DaleWatkins's avatar
DaleWatkins
Regular Visitor
5 years ago

Sorry it wasn't clear but that's exactly what I was after, thanks v-zhenbw-msft

 

I do have a follow up but I'll open a new thread for that, thanks again.

 

Edit: sorry it doesn't look like it works on months with only 2 years of data.

 

Edit: I was being special, just start my date table from the 1st month of data #facepalm.