• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Suggestions for charting monetary damages

D

dianacris

Member
I recently posted about a project I'm working on regarding tornado data from 1950-2013. I have been able to get my number of tornadoes by year chart working with my secondary value charted over it, as well as a chart of fatalities and injuries. The pivot tables feeding those charts are filtered by a slicer based on County. Everything is working nicely on that so now to the final step!

What I'd like to do is create a chart showing damages for the selected county(s). I'm not sure how best to display this information. The original data has a column for damages for each tornado, but the data from 1950-1995 is shown as a range (ie, $50K-$500K) and the data from 1996-2013 has actual values (ie, $15K or $300M). The data is by tornado and by county affected by the tornado. Because there could be more than one county, the tornado "key" value is repeated in my table, which means the damages are repeated as well.

I have gone through the data and put all of the actual values into ranges that correspond to the 1950-95 data ranges. Was that the correct way to move forward? I've attached a file with the basic data, a pivot table, and a slicer.

Any help and guidance appreciated!
 

Attachments

  • Tornado_damages.xlsx
    452.8 KB · Views: 3
Looks like the best way to handle it, going to ranges. Are the damage amounts divided correctly into county, or was it the total? E.g, a tornado cause $500k damage total, and was in Adams and Charlie county. Do both Adams and Charlie list 500k, or is it correctly split to say something like $300k and $200k?
 
That will certainly make things hard if you wanted to do totals.
Take a look at the attached, which is a re-arrangement of your sheet. I tried to move all the data/hard coded info to the left, formulas on right. I'm not sure what F, I, ckF, ckI, TI are about.
Then, in the PT, I grouped the years and set both column and row headers to display all labels, even with no data.
At this point, got a little stuck, as not completely sure what story we want to tell. Is your audience good with just the PT, or do they need a visual chart? Is time progression more important, or damage (again, damage total gets tricky due to above issue).
 

Attachments

  • Tornado_damages Proposal.xlsx
    515.9 KB · Views: 3
F and I are for fatalities and injuries. I used the chkF and chkI columns to identify unique records so that I don't have the repeated fatalities and injuries (which would skew the totals) just because there were multiple counties affected by the same tornado. (ex: 19530313.40.9) TF and TI are the total columns where I'm getting my total for each based on the result found in the chk columns. Just like the damages, the F&I are not broken out by county but rather are the same across all counties. I think my original group column was doing something similar to the chk columns but I was having trouble figuring out how to differentiate "$0 damage" from "exclude because this row is a repeat".

Yes, my colleague would like a chart. I was thinking of a total of the instances of each damage range? So, 5 times there were $50-$500K in damages?

Thanks for your help!
 
How about the chart in this one? Let's you compare # of tornados in each damage group, and compare between multiple cities. Obviously you can adjust the formatting/labels/placement.
 

Attachments

  • Tornado_damages Proposal.xlsx
    516.6 KB · Views: 6
thank you thank you thank you! Your example got me headed in the right direction. I'm going to forward the final file to my colleague so he can provide his feedback. I'm sure he'll be happy with it. (I am!)

:)
 
You must log in or register to reply here.
Back
Top