Total Count per month by Status

[MTPisaris]

Hi Guys,

Please i need your help urgently. I have a table with records of incidents with different status (Open, Assigned and Closed).

I will like to create a line and clusterred column chart to break the incidents down per total status per month. So the chart will have 3 columns per month and each column will hold the total count of the status for that month.

How can i achieve this?   Regards

[parry2k · Accepted solution]

@MTPisaris assuming you have table name called incidents with following fields

Date

Incident Id

Status

To achieve the result your are looking for

- select clustered column chart visual

- add date on x - axis

- add status on legend

- add incident id on value and make sure aggregation is set to count, or write a measure like below and put that on value

Incidents = COUNTROWS( TableName )

[dax · Accepted solution]

Hi,

Yes, you could refer to perry2k's suggestions, and use date hierarchy in axis or you could create a column for Year-month by expression :Column = YEAR(chart[date])*100+chart[date].[MonthNo], and use this in axis

Best Regards,
Zoe Zhi

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

[parry2k · Accepted solution]

@MTPisaris assuming you have table name called incidents with following fields

Date

Incident Id

Status

To achieve the result your are looking for

- select clustered column chart visual

- add date on x - axis

- add status on legend

- add incident id on value and make sure aggregation is set to count, or write a measure like below and put that on value

Incidents = COUNTROWS( TableName )