How to make a summary table

[Anonymous]

Hi,

I have an "Incident" table looks like this, note that I created the column (Opened At Month) in Power Query editor so that it is easier for future aggretation based on month:

Incident:

"Opened At"	"Opened At (Month)"	"Incident ID"	"Met SLA"	"Age (Days)"	"Duration(Secs)"
1-Sep-21	Sep-21	Inc090921	Yes	0.008	691.2
13-Sep-21	Sep-21	Inc130921	Yes	0.0009	77.76
2-Oct-21	Oct-21	Inc021021	Yes	0.0073	630.72
12-Oct-21	Oct-21	Inc121021	No	1	86400
2-Nov-21	Nov-21	Inc021121	No	0.9	77760
5-Nov-21	Nov-21	Inc051121	No	1	86400
11-Nov-21	Nov-21	Inc111121	No	1	86400
2-Dec-21	Dec-21	Inc021221	Yes	0.006	518.4
21-Dec-21	Dec-21	Inc211221	No	1	86400
8-Jan-22	Jan-22	Inc080122	Yes	0.0006	51.84

And based on this Incident table, I need to create an Incident Summary table that aggregates the number of incidents, and number of incidents that have met SLA etcs each month, it should look like this:

Incident Summary

"Opened At (Month)"	"Number of Incidents"	"Incidents that have Met SLA"	"Average Age (Days)"	"Duration(Secs) > 80000"
Sep-21	2	2	0.00445	0
Oct-21	2	1	0.50365	1
Nov-21	3	0	0.9666666667	2
Dec-21	2	1	0.503	1
Jan-22	1	1	0.0006	0

I tried to use DAX to create a new table: Incident Summary = SUMMARIZE(Incidents, Incidents [Opened At (Month]), and created a relationship between Incident and Incident Summary. I managed to figure out the calculation of "the Number of Incident" column = CALCULATE (COUNTROWS(Incident), USERELATIONSHIP(Incident[Opened At (Month], 'Incident Summary'[Opened At (Month)]))

However, I am stuck with how to calculate number of Incidents that have met SLA (Met SLA = Yes), I tried to use this formula to create the "Met SLA" column but it does not give me the right number: Met SLA = CALCULATE(COUNT(Incident[Met SLA

]), FILTER (Incident, Incident[Met SLA]="Yes"))

It would be really appreciated if anyone can let me know how to summarise the following metrics in Incident Summary table:
1. Incidents that have Met SLA

2. Average Age (Days)

3. Duration(Secs) > 80000

I also tried Pivoting from Query Editor but it didn't help. Much appreciated if anyone can point me right direction on how to properly summarise a table in PowerBI, either using DAX or other methods, I am still relatively new to PowerBI.  Thanks for very much for your help.

[Anonymous]

Thanks @ValtteriN , it works! Much appreciated.

[ValtteriN · Accepted solution]

Hi,

Instead of using FILTER in CALCULATE you can do it like this:

Table 3 =

SUMMARIZE(Aggregation,Aggregation[Opened At (Month)],"SLA",calculate(COUNTROWS(Aggregation),Aggregation[Met SLA]="Yes"),"Average",AVERAGE(Aggregation[Age (Days)]),"Over >80000",calculate(COUNTROWS(Aggregation),Aggregation[Duration(Secs)]>80000))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!