• 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.

Count unique values in filtered pivot table

dianacris

Member
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:
  • 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 believe the problem with the two above is that if there is a tornado with multiple counties, some of the rows have 0, and if one of the two counties I'm pulling is a record with 0, then it doesn't come up in the total.

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!
 

Attachments

  • Sample.xlsx
    317.5 KB · Views: 4
Hi:

I am not sure I fully understood your requirement. Please find the attached.

Hope this is what you are looking for.

Thanks
 

Attachments

  • Sample.xlsx
    321.5 KB · Views: 5
Thanks but that's still not what I needed. I'll try to explain a little better.

If you take the raw data, and copy it to another worksheet, filter the month for April, May, and June, filter county for Cleveland and Oklahoma, then you are left with a list of 122 records. However, if you highlight the duplicates in the index column, there are 8 records highlighted because 4 tornadoes are repeated. These tornadoes occurred in both counties. I need my pivot table to have a count of the unique tornadoes by year. The years 1975, 1999, 2003, and 2010 each are showing 1 tornado too many.

I've added a worksheet to this file which has the filtered data and the duplicates are highlighted. I hope this helps explain my problem so that you can help me solve this!

Thanks!!
 

Attachments

  • Sample_v2.xlsx
    328.3 KB · Views: 3
Hi:

I have changed the formula for counting unique values, Now your pivot is giving 106 for the filtered values, the reason is that your base data is having only 106 unique values as per the filter not 118. However, if you go to the raw data filtered tab I have used the same formula to count unique values to test my formula and it is giving me 118. I guess you should revisit your base data and see whether it is 106 or 118 for the pivot.

Thanks
 

Attachments

  • Sample_v2.xlsx
    315.8 KB · Views: 11
It is 118. Some of the records that should be included in the total are getting excluded because of the formula and I can't figure out how to stop that.

example:
in 1977, the formula has a 1 for Logan county and a 0 for Oklahoma county. The 0 is because that record is a duplicate.

Index | Month | Year | UniqueT | County | Damage
19770520.40.40 May 1977 1 Logan $50K-$500K
19770520.40.40 May 1977 0 Oklahoma $50K-$500K


When I filter the pivot for Oklahoma and Cleveland county, the tornado in May 1977 for Oklahoma county does NOT get counted because the formula shows a 0. It should still count that tornado. I need the pivot to show me unique tornadoes based on the filter, not overall (unless I'm not filtering of course).

Help! :confused:
 
Hi:

If you want get the desired results your count if function should be dynamic, in the sense the countif function should change dynamically as per the filter in your pivot, I am not sure this is possible using formulas. My formula was giving you 118 in the tab raw filtered tab because it had only 2 counties Oklahoma and Cleveland, but in your pivot tab there are other counties with same tornado ids which is why you got 106 as result, there is no issue with the logic of the formula , its just that your data is set like that.

Thanks
 
Nebu, yes the formula needed to be dynamic. Your formula's logic was right but I needed a formula to dynamically count the unique tornadoes after filtering. Sorry I was unable to articulate that better in my original and previous posts.

I was able to solve my problem by using a solution from Roger Govier to create a pivot table of my pivot table. First I created a pivot of my tornado data with just year and index as rows labels and then set the layout to tabular form with repeating data labels. This gave me a list of each year with each unique tornado ID. Then I created a dynamic named range based on the size/shape of the first pivot, and used it as the source for my original pivot table. I modified my slicers for month and county to filter the first pivot table, so the second pivot would count unique tornadoes by year. All I had left to do was add code to refresh the second pivot when the first was updated. Bingo!

I hope this helps someone else! Thanks!
 
Hi:

I guess the same slicer can control both the pivots. If not, then VBA is the only solution.

Thanks
 
Back
Top