Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
EClearyCA
New Member

Weekly crime count comparison to range

Hello,

I'm trying to create a dashboard to help track weekly crime counts and whether they fall within a certain range. I have pre-determined what the range should be per week per crime category and recorded the the low and high in a spreadsheet. The ranges are based off a running total by week for each category. 

Every week I'll upload an export from our RMS with the case number, date/time, and crime category code. Still learning/googling my way thru and I've managed to create a table showing the current week's running total crime count. But I'm having trouble trying to figure out how to compare the weekly crime count to the range and to conditionally format to show if the current week is within, above or below the range. 

Below is a sample of 15 weeks of the count range for crime categories/codes 100, 120, 220, and 240. I can reformat this if needed. 

WeekNum100 LOW100 HI120 LOW120 HI220 LOW220 HI240 LOW240 HI
112021201
212083757
31419712712
41431311161020
52861615211225
621091819291828
741092224372335
8512102227452740
9613122434512845
10715142538573149
11915162743643455
121015182950713858
131115183056813961
141316193261884164
151417203469944366

 

Here is a link to 3 months of (clean) sample data. https://docs.google.com/spreadsheets/d/1IB_N-JbOFna1CkmkGlo-H8LGm0JXCNTq/edit?usp=sharing&ouid=10745...

Any thoughts, help, guidance, pointing in the right direction would be greatly appreciated! Thank you!

1 ACCEPTED SOLUTION
hokeson
Microsoft Employee
Microsoft Employee

Hi, here is an example.

is that what you are looking for? 

hokeson_0-1655999695686.png

if you prefer another type of conditional fomrating you can change it in fx by bullet 3.

 

View solution in original post

3 REPLIES 3
hokeson
Microsoft Employee
Microsoft Employee

Hi, here is an example.

is that what you are looking for? 

hokeson_0-1655999695686.png

if you prefer another type of conditional fomrating you can change it in fx by bullet 3.

 

I figured it out! Thanks for the visual, it helped! Ended up using the line graph visual to show the high/low threshold with the running total by week. 

Yes! Teach me your ways please!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.