Forum Discussion
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.
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!
2 Replies
- ValtteriN
Community Champion
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! Thanks ValtteriN , it works! Much appreciated.