5 Replies Latest reply on Oct 29, 2018 11:54 PM by Eric Viglotti

    Filtered item next to overall total

    Eric Viglotti

      I want to have two columns of data, one for just a filtered selection of records and one for the totals without this filter giving me context on how the selection's total compares to the overall total.

       

      Using Super Store as an example, the first tab shows me this:

       

      Screen Shot 2018-10-15 at 12.29.39 PM.png

       

      We can see the total sales by product by region and with a total at the right. I want to total the sales for Central and West together and compare it to the overall total. This is easily solved with the "fixed" LOD function, so long as that product has sales for that region. Specifically looking at the above example, this works flawlessly for the first product, but this highlighted "1/4 folder party..." product now disappears from the 2nd tab:

       

      Screen Shot 2018-10-15 at 12.29.54 PM.png

       

      This makes sense because the FIXED LOD function only reports other data so long as there is a row in your actual data, but this doesn't work for my use case here. How can I structure this so it shows all products that had sales and just shows a $0 for the sales for just the selected regions so I can see that Central and West combined had 0% of the total $49.98 sales for the entire Super Store data? Is this even possible?

       

      My initial intuition was to skip filters entirely and just do an "if region in [Parameter of Regions] then sales else 0 end" function and put that next to the sales measure. However, Tableau doesn't allow multiple selections in a parameter and I need to expand this to a much broader use case, so simply having two parameters and doing an "or" clause isn't possible.

       

      Any other ideas? Would some type of funky union of the data to itself and some other functions comparing the two subsets of data work somehow?

       

      Thanks.

        • 1. Re: Filtered item next to overall total
          Simon Runc

          hi Eric,

           

          You could do something with the data, and this would allow you to get the zeros in, but this option is much easier, if you can live with the blanks!

           

          • 2. Re: Filtered item next to overall total
            Eric Viglotti

            Interesting, per the screenshot below, that almost works. It does list the empty row for the "1/4 fold party..." item, but it's not a "real" row of data, meaning that I can't do % of overall total calculation and have it return 0, even with the zn function. Furthermore, I can't do a "keep only" on this item because, again, it's not a "real row" of data.

             

            I think the real solution to this is to just use a parameter and an "if [parameter] = Central then sales else 0 end" clause and be done with this. The only downside is I would need multi-value parameters (per this idea: https://community.tableau.com/ideas/1313) to be able to do Central and West together as per this use case. I think ultimately a filter will filter out the data leaving no "real row" there that I can do anything with.

             

            And FYI, I posed this question to Tableau Doctor at the conference and the best we could come up with is some custom SQL added to the data to create a cross-join of every possible product/region combination filled with zeros so that you then always do have the "real row" of data you need for this to all work.

             

            Thanks!

             

             

            Screen Shot 2018-10-27 at 10.29.55 AM.png

            • 3. Re: Filtered item next to overall total
              Eric Viglotti

              I did do a somewhat interesting hack/workaround which is attached.

               

              I basically did a union of the data to itself and did some calculations for a new "region" and a new "sales" measure. That allowed me to put the filter you see here with "- Show Blanks" as a new region option. With that unselected, it looks like it did before with the row "missing". But with it selected, it basically substitutes zeros from the original data so that you now see all rows.  This isn't the best workaround though because a.) it's an odd, non-intuitive filter selection, b.) you then can't split this by region on the view without this all breaking. So I think I'll just accept that this isn't a real doable use case in Tableau (as compared to other tools) at least until there are multi-value parameters.

               

              Screen Shot 2018-10-28 at 5.17.15 PM.png

              • 4. Re: Filtered item next to overall total
                Simon Runc

                hi Eric,

                 

                Shame! but yes I agree I think the only way to do it (with current functionality) would be by cross-joining every product/region combination (with customSQL or a view in the database), and then left joining the sales back on (an maybe even by date if you also require this dimension).

                 

                Out in Beta, atm, are Set Actions, which do let you have multiple selections (so would work as a multi-select parameter in this case, I think)...so hopefully this makes it out to Tableau-real very soon.

                • 5. Re: Filtered item next to overall total
                  Eric Viglotti

                  Very valid point on set actions. I saw bits of this at TC18 and it looks promising. We shall see. Thanks!