3 Replies Latest reply on Mar 5, 2016 4:36 PM by Albert Suryadi

    Filter TOP n by action filter

    Albert Suryadi

      Hi,

       

      In my mapping dashboard I'm trying to create a top 10 city bar chart if I click on a particular state.

       

      However it doesn't seem to work properly. For example, when I click Queensland it's only showing Brisbane. When I remove the top n filter in the worksheet, then it display that there are actually more cities that should be included....

       

      Any thoughts? (workbook is attached)

        • 1. Re: Filter TOP n by action filter
          Mosin K

          Attached is the updated version.

          The way you had setup the filter, it will filter the Top 10 cities across the whole dataset, without taking into consideration the state you had chosen.

          The better option is to use INDEX() function, making it calculate down the table (across the cities in the state) and retaining top-10 values. For this to work, the city values should be sorted by the field you need the top-10 by. e.g. if you need Top-10 by sales, then sort the city values by Sum([Sales]) in Descending order.

           

          Tableau had a good tutorial on this which you can look up through forum search.

           

          citiyfilter.png

          1 of 1 people found this helpful
          • 2. Re: Filter TOP n by action filter
            Kenneth McBride

            I'm on a plane so my apologies for not posting a workbook. The above solution using the INDEX table calculation function will work great or you can simply add the action filter to context. Go to your bar chart sheet and right-click the state action that's on your filter shelf. Click the Add to Context option and you should be squared away.

             

            Adding that filter to context creates an in memory temporary table. As you select different states the top filter will run only against the rows within the context of your state selection.

             

            Hope that helps. Let me know if you have any issues with that solution.

             

            Ken

            1 of 1 people found this helpful
            • 3. Re: Filter TOP n by action filter
              Albert Suryadi

              Hi Ken,

               

              Both solutions work either using index or add-to-context.

               

              Which one would be more efficient with big datasets (i.e. pros and cons of each methods)?

               

              Thanks,

              Albert