Thanks Joe, I couldn't read your TWBX (I'm using 5.1), but I am already using exactly the IIF you suggest. What I can't figure out is how to count the number of rows that have the calculated Coverage Met = 1. Other than using a running table sum as shown in the picture. But what I really want is to be able to access that calculated count in another view on the same dashboard that uses a different level of aggregation. It has to be calculated at the sample level but I want it totaled at the project level.
Are you counting or summing your "Coverage Met" calc field?
Also, I took a look at your screen shot, and you have Nulls where I would have expected zeros.
I attached the worksheets I made as an image, and here is a link to it in Tableau Public: http://public.tableausoftware.com/views/coverage_1/Dashboard1
coverage.png 43.6 KB
(repost, first response seems to have gotten lost)
Thanks for staying with me Joe, you understand what I'm looking for and I was able to reproduce what you did, but my data has one extra level of complexity which is the rub. Coverage is itself an aggregation summing multiple records of partial coverage for each sample, not an element in the database that contains total coverage per sample. This forces Coverage Met to be an aggregation as well. So rather than three SUMs on my measure values shelf for my details view, it contains:
And my roll-up project view measures are similarly:
AGG(Number of Samples)
Where Number of Samples = COUNTD([Sample]) because there are multiple records for each sample so I can't use SUM(Number of Records). So at the roll-up level, Coverage Met compares the aggregated total coverage for all samples with 3.8 and returns 0 or 1.
Any additional suggestions or alternatives are welcome.
The reason my picture shows blanks instead of zeroes is because I used a custom number format of # to hide zeroes so the positive values stand out in a table full of zeroes. That's a neat trick that works because Tableau's custom number format uses Excel's custom number format of P;N;Z;T (a semicolon-delimited list of formatting strings for positive, negative, zero, and text values) so by only using a single # we only format and display positive numbers.
What you are looking for is an aggregate of an aggregate, and my preferred way to accomplish this in Tableau is with custom SQL. For Example, if your default data connection SQL looked like:
SELECT [Sheet1$].[Coverage] AS [Coverage], [Sheet1$].[Project] AS [Project], [Sheet1$].[Sample] AS [Sample], [Sheet1$].[Sub Sample] AS [Sub Sample] FROM [Sheet1$]
you could rewrite it to:
SELECT SUM([Sheet1$].[Coverage]) AS [Coverage], [Sheet1$].[Project] AS [Project], [Sheet1$].[Sample] AS [Sample] FROM [Sheet1$] GROUP BY [Sheet1$].[Project], [Sheet1$].[Sample]]
so your coverage starts out aggregated for Tableau.
Above, I am guessing you are summing the coverage values for each sub sample, you would edit the first line so it gives you the coverage amount you want returned at the sample level.
If you need any help, just post the default content of the textarea in the dialog when you edit the connection and change the option to Custom SQL.