cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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-21Sep-21Inc090921Yes0.008691.2
13-Sep-21Sep-21Inc130921Yes0.000977.76
2-Oct-21Oct-21Inc021021Yes0.0073630.72
12-Oct-21Oct-21Inc121021No186400
2-Nov-21Nov-21Inc021121No0.977760
5-Nov-21Nov-21Inc051121No186400
11-Nov-21Nov-21Inc111121No186400
2-Dec-21Dec-21Inc021221Yes0.006518.4
21-Dec-21Dec-21Inc211221No186400
8-Jan-22Jan-22Inc080122Yes0.000651.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-21220.004450
Oct-21210.503651
Nov-21300.96666666672
Dec-21210.5031
Jan-22110.00060

 

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
ValtteriN
Super User
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))
 
ValtteriN_0-1641971808173.png


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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Thanks @ValtteriN , it works! Much appreciated.

ValtteriN
Super User
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))
 
ValtteriN_0-1641971808173.png


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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

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