I created a table with Market and Product Type, and added that to Tableau as a data source. Then I created a new worksheet with the new datasource as primary, put the Market and Product Type in the view, and added SUM(Number of Records) from the CoffeeChain db as secondary. This gets us the same total of records, only doesn't have a 0 for "Tea" in "South". Going to Format… for that and setting the Special Value for Text to 0 gets us the 0.
If the relationship is hierarchical instead of many to many, you could do this using a primary group. See this KB article for details:
Example workbook attached.
Thanks Jonathan. Wish there was a way to do this without creating a new table, but this workaround works great. Really appreciate the help.
You're welcome! I did have a later thought that you might have a query on your underlying data to generate the header table, and then set that up as an extract that on Tableau Server would get regularly updated. That way when you add a new category(ies) you wouldn't have to have the additional task to update the table.
I like that idea. Thanks!
I was able to utilize your solution to solve a similar issue, but am now stumped with another problem.
To build off of the data set utilized here, is there a way to be able to modify the data so that zero values show up (i.e. Tea is zero in the South) and that I am able to filter by product?
The easy way to get Tea in the South to show up as zero is to set the Format->(chosen measure)->Pane tab->Special Values section->Text to 0.
Using the above method, to do the filtering by Product, you'll need to pad out the data for the primary to include product so that can be in the level of detail for the view. If you want Product in the view, then it's easy to start filtering on that from the primary. If you want to filter on Product but not have that generating headers in the view, then instead of SUM(Number of Records) from the secondary, you'll need to have Product on the Level of Detail Shelf use a table calculation. This is due to needing Product in the view to get the blend to support filtering at the finer level of granularity, and the aggregation happening at the Product Type level. I set this up in the padded w/product2 and padded w/product filter worksheets in the attached.
Something that wasn't made clear in the original series of posts is that the data is not padded at all because of the layout (all discrete dimensions on Rows). In a crosstab view, such as having Market on Rows and Product Type on Columns, then Tableau will do a form of padding called "domain completion" because it needs to generate a pane for every combination of the discrete values on Rows & Columns. In that case, there is a pane (that doesn't have a value) that can be accessed via a calc like ZN(LOOKUP(SUM([Number of Records]),0)). I set this up in the domain completion w/ZN LOOKUP in the attached.