I'm kind of guessing what you really need here.
Attached I have created a very basic example using the superstore sample data. (Workbook is 8.2, but will upgrade to any version you are on.)
Sheet 3 is a very basic count showing how many rows are in the data for each continent. (I did that count two different ways: COUNT(Continent) and [Number of records].) I also added a COUNTD() (which in this example will always be 1. But it shows the difference between COUNT and COUNTD, and shows that you can do the same thing for your example.)
Sheet 4 is a copy of Sheet 3, but it has a filter added. It's an "exclude" filter, so whatever continent you select, it gets excluded from the sheet. The user will select whichever values he wants to exclude, and your sheet will count up whatever is left.
Sheet 5 is a copy of Sheet 4 burt has an extra filter added. It is also an exclude filter, and it is on [Region]. Only "North America" has multiple regions. All the other continents have just one region: "International".
Now when you select regions, the COUNTD and COUNT count up what it left. I also added a COUNTD([Region] to show the effect on that field.
Is this the same sort of thing you are trying to accomplish?
Thank you for the quick response!
I think the exclude filter is close, but not quite what I am looking for. I have (actually, this time) attached to the OP a sample data set and a word draft of what the exec want the front page to look like. As you can see, to maintain intuitive usability and generate the other numbers on the page, the user needs to select the courses they ARE interested in (not those they want to exclude). I think I need the formula to do the excluding, rather than the filter?
1 of 1 people found this helpful
In the attached I have changed the behavior to work on "include" filters instead of "exclude" filters.
What I did requires Tableau 9.0 or higher to have access to LOD capabilities. The two calcs that do what I am doing here are LOD calcs.
The one that tells me how many regions are in the database acts outside of the filter behavior. The "FIXED" function of LOD says to query the whole data set.
The one that tell me how many distinct regions were in the selected table uses "INCLUDE". As it is written, It says to do a COUNTD of all the rows brought into the table.
In the SUPERSTORE data, all continents have only one region (international) except for North America, which has all 5 regions.
If you play with the filters. you will see that "Regions in database" never changes. It's always 5. If you add in other continents (besides North America) you'll see that the unique count of regions always remains at 1. If you add in North America, (and if you have only 4 regions selected, as I have it uploaded here) then the number of regions selected is 4.
Of course, [Database...] - [Selected...] will tell you how many were NOT selected.
The one condition you might have to accommodate is if your filter selections result in NO rows. (Take out North America, and then take out "international" from regions in this example.) If this is something that might occur, we can discuss how to handle that.
Thank you! That is exactly what we needed to make it work, and bonus, it works with *existing* data