How to create a dashboard to show increases

TaniaKat

I need to track the cost impact of changes per community. We track changes using a CRF # - so for example if I have CRF # 2024-015 that effects two communities, I would like to be able to say "hey, CRF 2024-015 creates a cost change at San Tan 40's Electrical Contract by $589.50 or 13% AND it also creates a cost change at San Tan 45's Fencing contract by $553.50. I keep trying to add chart widgets but I'm thinking maybe my change order log sheet is not set up correctly? My goal is to pull data pertaining to CRF's only to a dashboard for upper management to see. TIA

Change order log.png

Jgorsich

Your log seems correct, but presenting the data is where it will get interesting.

You've got a handful of choices here based on how many CRFs and communities you have -

For fairly small amounts of both, making a helper sheet that lists all possible CRFs as the rows and all possible communities as the columns would let you then have a set of sumifs formulas that say "sum all the costs where the CRF matches this CRF and the community matches this community". This would make it very clear, but if you've got more CRFs or Communities than you can fit on a screen, it becomes unrealistic.

For larger amounts of either, having dedicated dashboards per community or per CRF and then a single dashboard that summaries the total impact per community, total CRFs per community, and the total cost per CRF, total number of communities impacted by each CRF may be more viable.

For intermediary amount of each, you might be able to do something like first idea but instead of showing dollar figures in each cell just do a checkmark that says "This CRF affects this community" and then a link to more info….

Any way you slice it - you are almost certainly going to want a helper sheet that takes your log (which I wouldn't mess with) and reorders/rearranges it for the purposes of a graph or report for your dashboard. Don't underestimate the value of helper sheets! The best way to enter data is almost never the best way to hold it for your dashboard.