Forum Discussion

Mark8's avatar
Mark8
Frequent Visitor
2 years ago
Solved

averages help

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 

 

 

CategoryReported date
Clinical equipment / consumables26/05/2022
Documentation / health records20/12/2022
Information Governance 21/02/2022
Communication24/06/2022
Documentation / health records16/09/2022
Verbal Abuse29/12/2022
Actual Physical Assault12/09/2022
Systems of work26/09/2022
Breach of policy / protocol / procedure05/10/2022
Patient journey07/03/2022
Discharge issues/concerns05/10/2022
Discharge issues/concerns14/09/2022
Breach of policy / protocol / procedure14/09/2022
Breach of policy / protocol / procedure07/12/2022
Speech and Language Therapy04/10/2022
Flood24/10/2022
Flood24/10/2022
Treatment / procedure06/11/2022
Moisture Associated Skin Damage04/09/2022
Breach of policy / protocol / procedure18/11/2022
DAX Query
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    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

1 Reply

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    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