4 Replies Latest reply on Oct 11, 2016 7:51 AM by Stuart Dunlap

    Rank with LOD using Set Filter

    Stuart Dunlap



      I am trying to provide the Rank of a dimension member relative to the total set of members, however I would like to filter the view to show only the top N.  I realize may explanation may not provide all the clarity needed to answer my question so I've attached an example.


      The attached workbook has a worksheet called 'Data Bar Graph' that ranks State/City sales (or whatever parameter is selected with Evaluation Metric) by the total for all Categories.  A dynamic set is created that shows In/Out for the top N.  The 'Data Bar Graph' worksheet also shows the rank of the State/City for each Category as text on the bar graph... For example, Seattle, Washington ranks 3rd overall when Sales is selected as the Evaluation Metric, and Seattle also ranks 3rd in Furniture and Technology when Sales is selected, yet it ranks 4th in Office Supplies.


      My question pertains to what happens on the 'Data Bar Graph wFilter' worksheet.  When I apply the filter on my dynamic set of Top Geographies, my rank against the total population of State/Cities is now limited to just the top N.  For example, Detroit, MI (overall rank of 9) has Technology Sales that give it a rank of 17 against the total population of State/Cities (see the 'Data Bar Graph' worksheet), however in the 'Data Bar Graph wFilter' worksheet, Technology Sales for Detroit, MI rank as 13.  The rank in the 'Data Bar Graph wFilter' worksheet is not being calculated against the total population of State/Cities, only the filtered set.  I would have thought that since I'm not filtering in context, the LOD rank formula would still pick up the rank against the overall population in the 'Data Bar Graph wFilter' worksheet.


      Does anyone have any ideas on how to get the rank to calculate on the total population with a Set Filter being applied?



        • 1. Re: Rank with LOD using Set Filter
          Vasil Petkov

          Hi Stuart,


          How about filtering with a table calculation? You can use LOOKUP(max([Top Geographies]),0) running across State City Label. This way you limit what is being seen but not the data your rank computes on. The issue with the set filter is that it removes data for your rank dense table calc, so even though it computes over a fixed calculation it still goes by what's on the pane.


          Hope this helps.




          • 2. Re: Rank with LOD using Set Filter
            Lisa Li

            Hey Stuart,


            Similar to V's solution, if you right click on any "Out" value and hide it from your view (should hide all values that are Out), your table calc will be computed across all the data but only show the top N values. You'll limit what is being seen without affecting the calculation.




            Read about more Tableau solutions here!

            1 of 1 people found this helpful
            • 3. Re: Rank with LOD using Set Filter
              Stuart Dunlap



              My apologies... I missed your reply to my earlier post.  Lisa's subsequent post triggered me to revisit this.


              Your solution works great - thank you.  I can't say that I understand why the Set filter seems to behave like a context filter, but I'll research that more at the following link:

              Filtering and Hiding | Drawing with Numbers


              I also like Lisa's idea - or at least it brought to my attention that an Exclusion filter is different than Hiding something.  I never noticed those were two different operations.


              Thank you for your post.



              • 4. Re: Rank with LOD using Set Filter
                Stuart Dunlap

                Lisa - thanks for the post.  I'd given up on this topic, but came back to it after I saw your suggestion.


                I didn't realize Hide behaved differently than Exclude - so I learned something new!