cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## How to make a summary table

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.

1 ACCEPTED SOLUTION
Super User

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!

Proud to be a Super User!

2 REPLIES 2
Anonymous
Not applicable

Thanks @ValtteriN , it works! Much appreciated.

Super User

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!

Proud to be a Super User!

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors