March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
WeekNum | 100 LOW | 100 HI | 120 LOW | 120 HI | 220 LOW | 220 HI | 240 LOW | 240 HI |
1 | 1 | 2 | 0 | 2 | 1 | 2 | 0 | 1 |
2 | 1 | 2 | 0 | 8 | 3 | 7 | 5 | 7 |
3 | 1 | 4 | 1 | 9 | 7 | 12 | 7 | 12 |
4 | 1 | 4 | 3 | 13 | 11 | 16 | 10 | 20 |
5 | 2 | 8 | 6 | 16 | 15 | 21 | 12 | 25 |
6 | 2 | 10 | 9 | 18 | 19 | 29 | 18 | 28 |
7 | 4 | 10 | 9 | 22 | 24 | 37 | 23 | 35 |
8 | 5 | 12 | 10 | 22 | 27 | 45 | 27 | 40 |
9 | 6 | 13 | 12 | 24 | 34 | 51 | 28 | 45 |
10 | 7 | 15 | 14 | 25 | 38 | 57 | 31 | 49 |
11 | 9 | 15 | 16 | 27 | 43 | 64 | 34 | 55 |
12 | 10 | 15 | 18 | 29 | 50 | 71 | 38 | 58 |
13 | 11 | 15 | 18 | 30 | 56 | 81 | 39 | 61 |
14 | 13 | 16 | 19 | 32 | 61 | 88 | 41 | 64 |
15 | 14 | 17 | 20 | 34 | 69 | 94 | 43 | 66 |
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!
Solved! Go to Solution.
Hi, here is an example.
is that what you are looking for?
if you prefer another type of conditional fomrating you can change it in fx by bullet 3.
Hi, here is an example.
is that what you are looking for?
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!
User | Count |
---|---|
122 | |
98 | |
89 | |
74 | |
67 |
User | Count |
---|---|
139 | |
115 | |
114 | |
98 | |
98 |