11 Replies Latest reply on Feb 6, 2020 9:21 AM by Rohit Ghadge

    Ranking issue

    Nathan Haines



      So there seems to be a lot of info on ranking but none of it quite solves the problem i am having.


      Essentially all i want to do is rank a whole load of names by the number of sales they have made - which is the easy part. But when i filter it to look at one name, it always shows that person as #1 - but i would like it to keep the number they were at when it was not filtered. Any ideas?


      Thanks in advance!



        • 1. Re: Ranking issue
          Donna Coles

          HI Nathan - I struggled to get this to work - see my thread here.. its not really possible 'out of the box' How to show rank for filtered list while retaining rank of unfiltered list

          • 2. Re: Ranking issue

            Hi Nathan,


            Dimension filters in Tableau are applied before the table calculations. So filtering on the raw dimension would mean, Tableau filters that dimension first then executes the table calculation operation (opposite of what you want) which is why it always throws a value of 1, because there is only 1 row returned per customer. To fix this, you can create a calculated field on your dimension like:


            LOOKUP(MAX([Customer Name]),0) and use this field in the filter instead of the raw dimension filter (this is a table calculation as well). So tableau just looks at the customer name that is selected in the filter retaining the values from the other table calculation and hence giving the unfiltered rank. Hope this helps!


            Here is an article explaining the order of operation in Tableau: Tableau's Order of Operations


            10 of 10 people found this helpful
            • 3. Re: Ranking issue
              Donna Coles

              Thanks Pooja - this heped me get to what I was trying to achieve too.. maybe I didn't explain myself very well before!

              • 4. Re: Ranking issue

                Glad that helped, Donna!

                • 5. Re: Ranking issue
                  Lynne Sciandro

                  Hi Pooja,

                  Is there a way to have this apply across multiple worksheets on a dashboard? I would like to only have to select the filter once.

                  Thank you.


                  • 6. Re: Ranking issue
                    Michael Wray

                    I had the same issue, where i needed to filter across multiple worksheets.  To do this I used the method for filtering using a parameter, which in my case my field was Territory Name instead of Customer Name.


                    You do this by creating a new string parameter, mine was named "Select Territory."  The values are loaded with my territory names, and then I added a new option "All" to show all records.


                    Then I created a new calculated field named "Set Select Territory" with the following definition:

                    ([Territory Name] = [Select Territory]) OR [Select Territory] = "All"


                    Then you add this to your filter, and filter the worksheet where this is True.  Note, you have to create this calculated field on each data source.

                    I found when I wanted to start viewing my ranks by individual territories, that all the table ranks went to 1.  So, I modified my existing parameter calculated field "Set Select Territory" to use the lookup(max()) shown above, and this worked!


                    The new formula looked like this:

                    (LOOKUP(Max([Territory Name]),0) = [Select Territory]) OR [Select Territory] = "All"


                    3 of 3 people found this helpful
                    • 7. Re: Ranking issue
                      Kevin Ayers

                      Bringing this thread back from the dead as I have a similar (I believe) problem.


                      I have a list of words that I need turned into a WordCloud, which is easy enough. Each word is associated with a question number so that I can see the words most commonly associated with Question 1, Question 2, or Question 3. Once filtered to Question 1 I would like to limit the output to only the top X words, say 30.


                      I tried creating the calculated field as Pooja Gandhi shows above but the calculated field as stubbornly a measure, not a dimension, and won't be converted.


                      I'm sure that I'm missing some basic step but I can't figure it out for the life of me. Any help is greatly appreciated!


                      I've attached a file of sample data.

                      • 8. Re: Ranking issue
                        Nagasubramanian S



                        I am getting error as like this, If I try to do the same.



                        Error message is " Argument to MAX (an aggregate function) is already an aggregation, and cannot be further aggregated."


                        Can you please help to resolve this. Because in the dashboard the ranking is aligned to 1 automatically, to any customer being selected.


                        Thank you,



                        • 9. Re: Ranking issue
                          Zan Taylor

                          I was able to limit the number of words in the word cloud by filtering the dimension Word in the Marks window. In the filter, I selected the Top tab and then by Field and selected the parameter Top n Words parameter by Word and Count. This limited the number of words in the word cloud to the value selected within the Top n Words parameter.

                          Filter - Word.jpg

                          Tableau - Word Cloud Troubles.jpg

                          • 10. Re: Ranking issue
                            António Arantes

                            Hi, one question about this. First of all, thanks for the explanation. It was really useful and finally I can select one brand and still see the same results.


                            Now my question is how to connect this to the dashboard. I used the same calculation in two sheets, called "Vendor Filtered". It's the same formula, it's a matter of different measures (one is Rank in Units, other is Rank in Values). When I add the two sheets to a dashboard, I have two filters which are exactly the same and I don't have an option to apply to all worksheets and use just one.



                            • 11. Re: Ranking issue
                              Rohit Ghadge

                              Hi Pooja,


                              Your approach works perfectly.
                              However, I am trying to use a different dimension (XYZ) as a filter.

                              If I use (XYZ) as filter the newly calculated field (Calculated Customer) doesn't work.
                              I have 8 sheets that are using the dimension(XYZ) that I am tryin to use. Also, since the newly created calculated field (Calculated Customer) is a measure I cannot use it as a filter on the other worksheets.


                              is there a work around this problem?