1 Reply Latest reply on Apr 10, 2018 8:36 AM by Scott Kailey

    IN/OUT Set Not working for Top 5 by Sum of Balance

    Scott Kailey

      Hello,

       

      I have been racking my brain trying to figure out why my IN/OUT set is not working correctly for my bar charts.  I have tried different methods but I can't get anything to return the results that I want.

       

      My requirement is to show a chart of the top 5 lenders for a particular deal.  However, I need to keep the percentages based on the total lenders, not the top 5 filtered out.  This is why I ultimately tried the IN/OUT set method.  When I try, the only lender showing in the "IN" category is the top 1.  I have tried the methods in the following link with no luck.

       

      https://www.interworks.com/blog/jeverett/2014/05/12/3-ways-preserve-percent-total-within-filtered-dimensions

       

      I am either doing it wrong or something funky is going on.  I can't post a workbook because of proprietary data, but I have attached some screen shots.

       

      barchart.jpg

      set.jpg

       

      If you have questions or need more screenshots to see anything else, please let me know.

        • 1. Re: IN/OUT Set Not working for Top 5 by Sum of Balance
          Scott Kailey

          Of course, as soon as I posted I think I may have found a solution by figuring out an article I read on using the RANK function.  It was pretty easy once I figured out where to put it.

           

          Create the following calculation and compute is across the table.

              

               rank.jpg

           

          Next, drag that pill to my columns shelf and make sure to remove it from the marks shelf so it doesn't show as a label on the bar chart.

           

               shelf.jpg

           

          That was it.  No IN/OUT set necessary it appears and my percentages stay percentages of totals, not of what is filtered in the top 5.

           

               filtered.jpg

           

          I hope this helps someone else out because I spent way too much time looking for the answer that seems to be this simple.