Forum Discussion

PowerBiNoob37's avatar
PowerBiNoob37
Icon for Helper I rankHelper I
4 years ago
Solved

Count Values from one table using based on month and year and add to another table (Pbix included)

Hi,

 

I have a bit of incident data coming from a sharepoint list, every row contains a date of incident and incident type which is a choice field.

 

Here is a sample of my data

 

 

So i need to count the amount of incidents every month and display them in a datamatrix like the following excel image below, i understand i can use a datamatrix and use the totals however i dont want that i need to create a new table that counts these values and displays them in the following way for futher analysis.

 

 

Here is my pbix file

 

https://1drv.ms/u/s!As_sc_x3g6_qkGn9-_Z88kpGqr3P

 

Any help would be appreciated.

Need Help
  • HotChilli's avatar
    HotChilli
    4 years ago

    Power Query:

    Add a column for 1st day of the month (can do this from the interface->Select date column, go to Add Column-> From Date and Time on the ribbon)

    Remove the original date column.

    Group By StartOfMonth and Incident using Count Rows as aggregation.

    This gives a 3 column table which is pretty good for storage and analysis.

    If you want a table as shown, then Pivot the IncidentType column

3 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Power Query:

    Add a column for 1st day of the month (can do this from the interface->Select date column, go to Add Column-> From Date and Time on the ribbon)

    Remove the original date column.

    Group By StartOfMonth and Incident using Count Rows as aggregation.

    This gives a 3 column table which is pretty good for storage and analysis.

    If you want a table as shown, then Pivot the IncidentType column

    • PowerBiNoob37's avatar
      PowerBiNoob37
      Icon for Helper I rankHelper I
      4 years ago
      HotChilli wrote:

      Power Query:

      Add a column for 1st day of the month (can do this from the interface->Select date column, go to Add Column-> From Date and Time on the ribbon)

      Remove the original date column.

      Group By StartOfMonth and Incident using Count Rows as aggregation.

      This gives a 3 column table which is pretty good for storage and analysis.

      If you want a table as shown, then Pivot the IncidentType column

      Thanks,

       

      I didnt check my emails all day and so i didnt see your response and only came back here to post my answer, ill give you the solutions, because your right that would have been correct. But heres what i did to get the same results

       

      I created a simple distinct measure in DAX

       

      TotalInjuries = DISTINCTCOUNT(InjuriesTable[Injury Type])

       

      and then created a table referencing the measure and using several if statments to group it by, it then created a table like i wanted and referenced the date as i set up a one to many relationship.

       

      IncidentTotalTable = ADDCOLUMNS(IncidentTable, "Date", DATEVALUE(IncidentTable[InjuryDate]), "FAI" , IF( [Injury Type] = "FAI", [TotalInjuries] ), "MTI" , IF([Injury Type] = "MTI",  [TotalInjuries]), "LTI" , IF([Injury Type] = "LTI", [TotalInjuries]))

       

       I like your way better, except i would have had to create another table.

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi PowerBiNoob37 ,

     

    Whether the advice given by HotChilli  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.


    Looking forward to your feedback.


    Best Regards,
    Henry