1 2 Previous Next 25 Replies Latest reply on Apr 6, 2018 8:18 AM by Candace Bell

    Top N within multiple measures as filter possible?

    JayC

      Hi All,

       

      This forum has been really useful in fixing most of my questions i have posted.

      Here I am with another Q -

       

      Requirement:

      1. Want Top/Bottom N within multiple measures.

      Say - I have a filter on measures names - "Sales", "Quantity" & "Profit". I would like to have Top/Bottom customers on basis of the filter selection.

      As per the user selection of category top/bottom customers has to flick

      (i believe this can be achieved using Parameter but am unsure how to integrate this thing to work as per need)

       

      2. Would be nice to have ability of flipping between Top/Bottom alongside its rank being displayed

      3. Also requirement is to have a bar chart in display as per the rank

           Example- Selected filter is Top 25 (using slider) on a measure say "Sales"

      We should have Top25 customers within sales and these rows should have a bar chart next to it symbolized by its size and color on basis of their rank

       

      I have previously created top n within one category using this video link here-

      https://www.youtube.com/watch?v=CAZ3IAJEuCI

      But requirement has changed now

       

      Please can someone -  kindly help me in getting the requirement. Will be very very helpful.

       

      Note- Apologies for lengthy post.

        • 1. Re: Top N within multiple measures as filter possible?
          Steve Martin

          Hi Jayachandra,

           

          First off, please do not apologise for the lengthy post, it is a quality question describing your requirement - saying this, I just hop I have understood your requirements properly

           

          It does sound as though you want to be able to switch the top n fro your metric on the fly if this is correct, that is what I have built you.

           

          Unfortunately I am using Tableau Public at home so I have instead linked to my upload but the way this wa put together was to create a parameter to hold the metrics, and a calc to surface the chosen metric; once in place, the top has been applied to the metric selection field as per usual.

           

          To be sure, I have also created the same on several dimensions so you can easily jump across different dimensions and then look at different measures.

           

          TopN Multiple Measures viz

           

          [Re-visiting the title makes me wonder whether I have this right]

           

          Steve

          • 2. Re: Top N within multiple measures as filter possible?
            JayC

            Hey Steve,

             

            Thanks for going through my requirement and also making one similar with sample data.

             

            Would it be possible to -

            1. Have top and bottom in one filter itself so the user will have more choices thrown - (Wondering to know feasibility of having this) 

            2. Have something like Rank shown to each row (See attached)

             

            Once again I truly appreciate your work and time dedicated.

             

            sample.png

            • 3. Re: Top N within multiple measures as filter possible?
              Steve Martin

              Hi Jayachandra,

               

              I have updated the workbook to include the choice of top/bottom n ready for downloading - apologies in my haste I have not added comments to the calcs.

               

              So the way this is built:

               

              1. Create a parameter of known measures I called it 'Measures', I chose 'Sales', 'Profit' and 'Shipping Cost'
              2. Repeat Step 1 for your list of dimensions eg 'Region', 'Customer Name', 'Product' etc
              3. Then create your calculated fields, one per parameter, so for the measures, it uses a simple case statement:

                Case [Measures]
                        When 'Sales' Then [Sales]
                        When 'Profit' Then [Profit]
                        When 'Shipping Cost' Then [Shipping Cost]
                End
              4. Do this for your Dimensions parameter too, these two fields materialise the parameter selection into the view
              5. Create a new parameter call it 'Top n' for now, you can call it something else later, set it to be an integer and as a Range for the allowable values; set the values as min 1, max 10 and step-size as 1
              6. Going back to your view, add the Dimension calculated field to the rows and the Measures calculated field to the values
              7. On the Dimensions calculated field, choose filter the select the Top tab then choose By field 'Measures' and in the number currently defaulted to 10, change this to be your [Top n] parameter

               

               

              To this point, you have created a semi-dynamic vis able to select a differing list of dimensions, measures and able to set the top n. Moving on to the next part of your requirement, that is to be able to choose between Top n & Bottom n requires a little more work.

               

              Repeat steps 1-6 above and then:

               

              1. Create a new parameter call it 'Direction', set it as a String list with values 'Top n' & 'Bottom n'
              2. Create a new calculated field call it 'Direction Selection' (Mine was called Rank Measure); this time we shall be using the rank field to determine the list and with a simple switch we can determine the direction:

                If [Direction] = 'Top n' Then
                          If Rank(Sum([Measures]),'desc') <= [Top n] Then 'In' Else 'Out' End
                Else If Rank(Sum([Measures]),'asc') <= [Top n] Then 'In' Else 'Out' End
                End
              3. The switch between 'desc' and 'asc' determines the direction of the list
              4. Simply drag this new field to the filters and select in


              And now you have a semi dynamic chart where your users can alter the dimensions, measures, top & bottom n and the list of n, based on a pre-determined list of items by you.


              Steve

              1 of 1 people found this helpful
              • 4. Re: Top N within multiple measures as filter possible?
                JayC

                Hey,

                 

                I was able to replicate your work with steps given above, but was facing issue in flipping top n bottom view part.

                Have followed your steps but issue faced is attached

                 

                and must admit ur awesome sample.png

                 

                Can you please post your workbook on Tab public for download? I will look into it and try replicating same

                • 5. Re: Top N within multiple measures as filter possible?
                  Steve Martin

                  Hi,

                   

                  Thanks for the gratitude, I'm just glad I could help.

                   

                  Going back to your list of questions above, please can you explain a little more what it is you are trying to achieve with point 1; Point 2 on the other hand can be easily achieved; to work with your parameter it would look quite similar to the calc you are using for the filter:

                  If [Direction] = 'Top n' Then Rank(Sum([Measures]),'desc')

                  Else Rank(Sum([Measures]),'asc')

                  End

                   

                  Once you have created this, drop it to the values besides any other values you have, probably call it 'Rank'.

                   

                  Finally, on your error, the Rank() function can only be used with measures or rather items that can be aggregated, but it looks like you may have string data in your measure for which Tableau is unable to compare.

                   

                  If it helps, I have re-uploaded the workbook to include the ranking for your point 2 above - this also features a sort on the dimensions by the sum of the measure so the rank and data shall always be sequential.

                   

                  Steve

                  • 6. Re: Top N within multiple measures as filter possible?
                    JayC

                    Hi Steve,

                     

                    I was able to follow your steps and get the desired result exactly as i wanted. You made my day

                    Apologies for asking once again would it be possible to have rank shown when view is set with horizontal bars. like one below-

                     

                    Your dashboard is on text table view and thereby its showing its rank, in my case i (client) want it to be set to horizontal bar view, i do agree its basically sorted on the basis of rank but client wants rank number also to be displayed.

                    Only if time permits you can answer this since you have already spent lot of time for me. 

                    Thanks 1ce again for the support!

                    Have a great weekend!

                    • 8. Re: Top N within multiple measures as filter possible?
                      Steve Martin

                      Hi Jayachandra,

                       

                      Once wrapped in string tags then yes. Uploaded once again - sheet name Horizontal; your calc is:

                       

                      '(#' + Str([Rank]) + ')'

                       

                      This creates a discrete measure that can now be added to the rows field similar to the dimension pill.

                       

                      And thank-you Shawn.

                       

                      Steve

                      • 10. Re: Top N within multiple measures as filter possible?
                        JayC

                        Hey Steve,

                         

                        Apologies requirement has deviated today-

                         

                        Fields in Metric selection filter say are: Customers, Customers YOY, Transactions, Transactions YOY

                         

                        1. In the metric filter drop down from the above fields - I have fields that has to be shown in % format and other in standard number format. how can we split formatting between different metric coming under same filter.


                        2. Would it be possible to do the below-

                            Length of the bar would be the “volume” of the metric type chosen and color of the bar would be the         YoY change in that metric type

                          • Ex. if Customers  OR Customer YoY is chosen for ranking, the length of the bar is based on Customers  and the color of the bar is based on Customer  YoY
                          • if Transactions OR Transactions YoY is chosen, length of the bar is based on Transactions and the color of the bar is based on Transactions YOY

                         

                        If possible please help!

                        • 11. Re: Top N within multiple measures as filter possible?
                          Steve Martin

                          Hi Jayachandra,

                           

                          Sounds a bit like scope creep to me.

                           

                          We can do this though in order to achieve this we would be changing the fundamental calculation and we would need to consider two calculations, the first to display the result that is added as a string to include comma separation and the percentage sign and a second that remains as a measure that is the ultimate driver of the calculation.

                           

                          I can put something together once I get home this evening.

                           

                          For point 2, this should be a little more easier to achieve as you would simply add your calculation for volume to the size field and the YoY difference is added to the colour.

                           

                          Steve

                          • 12. Re: Top N within multiple measures as filter possible?
                            JayC

                            Hey Steve,

                             

                            Thanks for responding back!

                            To be accurate this is the exact requirement from my client-

                             

                            A) would like a parameter control with “Rank by” with choices Customers, Trxns, Principal, Revenue, Customer Change YoY (use delta symbol if possible), Txn Change YoY, Revenue Change YoY  (all 8)

                             

                            • Length of the bar would be the “volume” of the metric type chosen for ranking, and color of the bar would be the YoY change in that metric type
                              • Ex.  if Customers   OR Customer Change YoY is chosen for ranking, the length of the bar is based on Customers and the color of the bar is based on Cust ChangeYoY

                            B) Formatting customers,Trxns, Principal, Revenue as number and Txn Change YoY, Revenue Change YoY etc as %

                             

                            C) would like the ability to flip between top N and bottom N

                             

                            Would be really helpful if you can help me in achieving this.


                            **A+C are important**

                            **B is less important**

                            • 13. Re: Top N within multiple measures as filter possible?
                              JayC

                              Hi Steve,

                               

                              Can you please help me with the above. I haven't got answers from anywhere else and am stuck!

                              • 14. Re: Top N within multiple measures as filter possible?
                                Steve Martin

                                Hi Jayachandra,

                                 

                                Apologies, I hadn't forgotten you, just been away from work ill. I shall definitely get something over to you this evening once I return from work.

                                 

                                Steve

                                1 2 Previous Next