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 y...
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

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper 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