I am back with my tornado data report that I posted previously about in the charting forum. I got everything working and it looks great (thanks to Luke M for the assistance), and then realized that I've got some errors in my "tornado count" totals because the pivot table is not counting unique records, or at least it isn't under some conditions.
My data is information about tornadoes in Oklahoma and it specifies which county the tornado occurred in, the amount of damage in dollars, fatalities, and injuries. I have a pivot table which has tornado year (rows for 1951-2013) and then the values area is counting the index number assigned to each tornado. However, the data does have repeated index numbers because there is a column in the raw data for the county where the tornado was reported. Because tornadoes move around, there could be tornado 19650831.40.68 which took place in Oklahoma and Cleveland counties. (There's essentially a dual key here -- index and county.) When I use a slicer to filter the pivot table for months April, May and June and then use another slicer to filter for the two counties mentioned above, I get a count of 122 but it should 118 because there are four tornadoes that occurred in both counties I'm looking for during the months I'm filtering for and those tornadoes are counted twice.
What I've tried:
I've uploaded a file with the basic data, my pivot table, and my slicers.
I sure hope someone can help me! Many thanks in advance!
My data is information about tornadoes in Oklahoma and it specifies which county the tornado occurred in, the amount of damage in dollars, fatalities, and injuries. I have a pivot table which has tornado year (rows for 1951-2013) and then the values area is counting the index number assigned to each tornado. However, the data does have repeated index numbers because there is a column in the raw data for the county where the tornado was reported. Because tornadoes move around, there could be tornado 19650831.40.68 which took place in Oklahoma and Cleveland counties. (There's essentially a dual key here -- index and county.) When I use a slicer to filter the pivot table for months April, May and June and then use another slicer to filter for the two counties mentioned above, I get a count of 122 but it should 118 because there are four tornadoes that occurred in both counties I'm looking for during the months I'm filtering for and those tornadoes are counted twice.
What I've tried:
- helper column in the raw data with =1/COUNTIF(A:A,A2) and then use my helper column in my pivot, but my total comes up short -- it gives me 106
- helper column with using SUMPRODUCT and result is same as above -- 106
I've uploaded a file with the basic data, my pivot table, and my slicers.
I sure hope someone can help me! Many thanks in advance!