averages help

[Mark8]

I have a table with 22,000 rows of data in a category

I have a list "Category" , "reported Date" that's it. 22,000 in my list

there are 83 different category's in my list.

try to get Average per year and month.  i don't know what i am doing wrong. 

thank you 

Category	Reported date
Clinical equipment / consumables	26/05/2022
Documentation / health records	20/12/2022
Information Governance	21/02/2022
Communication	24/06/2022
Documentation / health records	16/09/2022
Verbal Abuse	29/12/2022
Actual Physical Assault	12/09/2022
Systems of work	26/09/2022
Breach of policy / protocol / procedure	05/10/2022
Patient journey	07/03/2022
Discharge issues/concerns	05/10/2022
Discharge issues/concerns	14/09/2022
Breach of policy / protocol / procedure	14/09/2022
Breach of policy / protocol / procedure	07/12/2022
Speech and Language Therapy	04/10/2022
Flood	24/10/2022
Flood	24/10/2022
Treatment / procedure	06/11/2022
Moisture Associated Skin Damage	04/09/2022
Breach of policy / protocol / procedure	18/11/2022

[bhanu_gautam · Accepted solution]

First extract Year and month from reported dateCreate Year and Month Columns

1. In the Power Query Editor, add new columns to extract the year and month from the Reported date.
   Click on Add Column > Date > Year > Year to create a Year column.
   Click on Add Column > Date > Month > Month to create a Month column.
   Close and apply the changes to load the transformed data back into Power BI.
   

   • Then create a measure for AverageIncidents

     AverageIncidentsPerYear =
     DIVIDE(
     COUNTROWS('YourTableName'),
     DISTINCTCOUNT('YourTableName'[Year])
     )

     Similarly for month

     AverageIncidentsPerMonth =
     DIVIDE(
     COUNTROWS('YourTableName'),
     DISTINCTCOUNT('YourTableName'[Year]) * 12
     )

@Mark8