6 Replies Latest reply on Mar 6, 2017 11:35 AM by Yuriy Fal

    Show Value when Nulls caused by filtering

    Jacob Turner

      Good afternoon,


      I've been scouring the forums for a while now, and I can't seem to find anything that helps me with my issue, so I'm going to throw it out there and see if anyone has any ideas.


      I have a product list for a client that has a number of sales metrics, filterable by store, along with a second set of the same metrics that compare the region totals to the selected store. For simplicity, let's say it compares sum of sales, filterable by store, to the sum of sales in the region, by item. For the first metric, I just use a regular SUM([Sales]) calc that obviously gets filtered down to the store level as selected. For the region sales, I've used an LOD formula: { FIXED [Region], [Product Name]: SUM([Sales]) }. For the most part it works perfectly.


      The problem is that the client wants to be able to filter down to a store and see the region totals for all products in order to determine whether they should sell that product in that store, based on the region metrics. This works fine for instances where the data has zero or null values for each product in each store, but in cases where there is no data for a product in a store at all, Tableau sees that as having no records when filtered, even if there is data for that product in another store within the zone. For example, in the attached, I want to select California to see what is and is not selling in that state, then compare those sales numbers to the overall region numbers by product. I can go to Analysis->Table Layout->Show Empty Rows to see all products, but any product with no sales in California will show blanks for both my Sales and Region Sales columns, ignoring my LOD formula.


      I understand why Tableau sees it this way (in its view, there are no records in California for some products), but I need it to still show me the Region sales, as per my formula. Any thoughts?