Below is an explanation of my IQR Clustering Analysis in Power BI in Excel hotspot model analysis applied to over 190,000 records of 311 Toronto Customer Initiated Service Request Data from January 1 to April 30, 2026.
Model Input:
The input data for this model analysis was an Excel worksheet (or it could be queried using PowerQuery M language from or located in some other place like a database) with 26 columns and 533 rows of data.
- Column A of the Excel worksheet lists each of the 533 reasons (Service Request Types) why a resident of Toronto has contacted 311 Toronto to complain or make a request over the four month period studied.
- Columns B-Z column headings are the names of each one of the 25 City of Toronto wards.
- Each cell contained a raw count of the number of 311 Toronto Customer Initiated Service Requests made by residents of a City of Toronto ward about a specific Service Request Type.
Model Output:
The output data for this model analysis is shown in an Excel worksheet that you can email me to obtain: it still has 26 columns and 533 rows of data (buy I do also have output without the raw counts overwritten).
- Instead of raw counts for the cell values, one of the following 5 text labels are used: 'High Outlier', 'High', 'Normal', 'Low', 'Low Outlier'.
- The 'High Outlier' values in cells indicate that, based on distribution of raw counts of service requests (across Service Request Type) received by 311 Toronto within a given ward, complaints for a specific Service Request Type in a ward have been found to be significantly, statistically high based on the IQR Rule theory of the box & whisker plot.
Model Conclusion:
- Based on the attached Excel output sample, my analysis revealed that not all City of Toronto Wards are the same in terms of the time, energy and resources that the City must spend fixing the various service requests logged by residents.
- Animal Noise complaints are significant issue in only 3 of the 25 City of Toronto Wards for the first four months of 2026: Parkdale-High Park (04), Toronto Centre (13) and Toronto-Danforth (14). It is for this reason, that their cell values are shaded yellow and have the words 'High Outlier' written in red.
- Residential / Yard Waste / Missed proved a significant issue for only 2 Wards and their cell values are shaded yellow with the words 'High Outlier' written in red.
The Hotspot Analysis Spreadsheet Template used to automate this analysis works on over 8,000 columns and as many rows of data as your version of Microsoft Excel accepts. The process will run as fast as your computer chip can handle. And, this IQR analysis process is an easier to apply and interpret segmentation process than either K-means Clustering or Hierarchical Clustering.
No CommentsBe the first to comment
