13 Replies Latest reply on Oct 29, 2014 4:19 PM by Ville Tyrväinen

    Rank by Top N Percent

    Ellen Hellblom


      I am trying to find out the number of contracts that account for the top 10% of spend, but every time i use the rank percentile (sum[spend]) calculation it comes up with the top 10% of contracts (i.e. we have 700 contracts and 70 contracts appear that account for >10% of total spend).

      Any ideas how to fix this?

      Thank you for your time and help.

        • 1. Re: Rank by Top N Percent
          Jonathan Drummey

          Hi, You haven't given enough information for us to give you an explicit answer, see http://community.tableau.com/docs/DOC-5065 for a general explanation.


          Diagnosing what is happening with table calculations is complicated because they are dependent on a number of factors, please provide a packaged workbook with some sample data and a mockup of your desired results. If you are unable to provide that, in order to help you further then we'd need to see a screenshot of your entire view (including Rows, Columns, Pages, Filters Shelves, and the Marks Card), see the formulae for your table calculations, the entire Compute Using settings of every table calculation (including any nested table calculations), whether Show Missing Values, Show Empty Rows/Columns was turned on, and whether you've turned off Ignore in Table Calculations for any discrete measures. Rather than you having to figure out all those things and type them in, it can be easier for you to post a packaged workbook and easier for us to look at all those different factors.

          • 2. Re: Rank by Top N Percent
            Ellen Hellblom

            Sorry about that - I'm new to this but thank you for your reply.


            Please see attached workbook. I hope this is sufficient enough for you?


            I would like to calculate the number of contracts that correspond to the top 10% of spend - So what I am trying to achieve is a slider where I can slide to N percent i.e. 10% and it will give the Top 10% based on 'Spend'

            I have looked at some blogs without success - either they are not displaying what I want or it have been unsuccessful.


            I would then also like to use the same Slider in the Dashboard so when I show Top 10% of Contracts, the second graph(by Company name) will follow and show the Companies linked to theses Contracts.


            My apologies if I haven't been clear enough but I really appreciate your help!

            • 3. Re: Rank by Top N Percent
              Jonathan Drummey

              You've got two measures on the By Contract worksheet, Spend and Covered Value, set up in an unstacked bar chart.Are you only looking to sum up the Spend measure only, or both?


              The total SUM of spend is 233M, when you say top 10% of spend are you looking for all the (largest, presumably) contacts that fit within 10% of that 233M? I'm guessing that from what you said in your first post, an alternative is to identify the top 10% of contracts, i.e. the first 73 of the 732 contracts? Or is there something else?


              Also, in your initial post you said you wanted to know the number of contracts, what do you want to do with that number?

              • 4. Re: Rank by Top N Percent
                Ellen Hellblom

                I am looking to sum up the Spend measure only as this is requested by the client.


                I have managed to identify the 10% of contracts, i.e. the second way you mentioned - this is not included in the extract I posted as this was only a dummy version I made in order to show you. So yes, I wish to do it by the first way you mention - top 10% of spend within those 233M.


                Sorry for not being clear enough!

                • 5. Re: Rank by Top N Percent
                  Jonathan Drummey

                  No problem, writing how we want to transform the data into a visualization can be like dancing about architecture.


                  You hadn't specified what you wanted to do once you'd identified the contracts in the top N %, so I just created a set of calcs to flag that. In the attached workbook, I did the following:


                  1) Created a parameter so the user can select the %.

                  2) Created a Total Spend calculated field with the formula PREVIOUS_VALUE(WINDOW_SUM(SUM([Spend]))), the PREVIOUS_VALUE() is an optimization. That gets a compute using on the Contract.

                  3) Created a Spend in Threshold calc with the formula:


                  //expects dimension (contract) to be sorted in descending order
                  //compute using will be on the contract
                  //the first address will always be included, even if the threshold is too small
                  //after that only if the spend meets the threshold
                  FIRST()==0 OR RUNNING_SUM(SUM([Spend])) <= [% Threshold]/100 * [Total Spend]


                  Then I built a workout worksheet to validate everything worked, and put the Spend in Threshold calc in your view:


                  Screen Shot 2014-10-15 at 7.02.32 AM.PNG


                  Another way to think about what's in the top N% or not is something like a pareto chart, so I built that as well. I used a Measure Names/Values set to get the bars (and turned Analysis->Stack Marks->Off), then used the dual axis for the pareto calc, see Using Pareto Analysis | Tableau Software for details on that. To indicate what met the threshold, I took advantage of Tableau's ability to have multiple discrete pills on the Color Shelf to change the mark color based on whether the Contract was in or out of the threshold. Finally, I added a reference line to show the selected threshold:

                  Screen Shot 2014-10-15 at 7.09.43 AM.PNG


                  If you have any questions, let me know!




                  [20141015 - edited per Ellen's comments to accurately use Spend instead of Sales in the measure descriptions]

                  • 6. Re: Rank by Top N Percent
                    sree rao


                    I hope you are excepting top N records, means  top N sales like , have a look on attached twbx file , it meet the exception ..



                    Sreenivsa rao.Aerasangi..

                    • 7. Re: Rank by Top N Percent
                      Ellen Hellblom

                      Fantastic! That was exactly what I was looking for - never seen calculations like that so thank you for showing me and thank you so much for taking you time to solve this, I really appreciate it!


                      However, just to double check - you said [Total Sales] but I assumed you meant [Total Spend] as there were no 'Sales-field' and taking the 'Spend' worked?
                      • 8. Re: Rank by Top N Percent
                        Ellen Hellblom

                        Thank you for your help and time but the previous answer helped me to solve the problem!

                        • 9. Re: Rank by Top N Percent
                          Jonathan Drummey

                          You're welcome! Yes, I meant Total Spend instead of Total Sales, that was my mistake. I'll edit the post to be accurate.

                          • 10. Re: Rank by Top N Percent
                            Jonathan Drummey

                            Hi Sreenivsa,


                            I'm glad you're trying to help out on the forums! I've got a few suggestions to help you help others:


                            1) Whenever possible, work with the data that the original poster provides. Almost every time I think that it will be easier to set up something using another data set, I end up missing something or creating confusion on the part of the person I'm trying to help.


                            2) When looking at a problem, always ask yourself what is being counted/summed/aggregated? What is being compared? And then use those answers to break down the fields required into aggregate measures or dimensions (or table calcs). The original problem here was that Ellen's first attempt (and the possible solution you'd posted) was counting the members of the Contract dimension (or distinct combinations of Area Code & Market in your possible solution), when what Ellen wanted was to identify the Contract dimensions based a % of a measure, in this case the total spend. (Which given the view had to be a table calc or use a different technique to get that total number). That was the key that led towards the solution I created.

                            3) Always always always pay attention to the dimensions in use in the view, especially when table calculations are involved. In Ellen's original workbook, there was one dimension in the view (Contract), in the possible solution you'd posted there were two dimensions (Area Code & Market). Table calculations can completely change results based on the presence (or non-presence) of dimensions in a view, so we have to be super-careful about them. That's a big reason why working with the original poster's data works better.


                            3) In your post, you referred to "records". In Tableau, "records" is an ambiguous term that almost always requires some clarification about context. "Records" could refer to the records (rows) in the data source, the number of records returned by a query that Tableau issues to the data (and there could be many queries & subqueries for a single view between the primary source, secondary sources, context filters, and top & conditional filters), or the number of "rows in the partition" aka "addresses" in a given partition that have meaning for table calculations, or the rows in a crosstab that we see in the display after all of the above happens. So whenever you are using "record", please be clear about what you are referring to.


                            4) This is the big one: A common attribute of the most valuable and most commonly referred-to forum answers is that the person answering takes time to explain the "what, why, and how": The "what" is what to do in order to get to the results. The "why" is why that solution works among all possible solutions, since there are often multiple solutions for a given problem in Tableau.  And the "how" is how Tableau works. It's drawing from that metaphor "Give a person a fish, feed them for a day. Teach a person to fish, feed them for a lifetime."


                            Doing that isn't only about helping the questioner. For me, taking that time to write out the explanations also helps refine my own thinking, sometimes leads to further questions, and ultimately improves my own skills. And if you're answering questions to help build a reputation for your Tableau skills (as some people are), having longer responses makes it more likely that your answers are likely to show up in search results (because there's more text to go from), which increases your own name recognition and improves your reputation. Having one-line posts that are "take a look at the attached" won't accomplish any of these things.





                            • 11. Re: Rank by Top N Percent
                              sree rao


                              Thanks for your great advise it help lot to analyze my self .. and I did not understand why you write this long story ,about this .. any how thanks again..



                              Sreenivasa Rao.A


                              • 12. Re: Rank by Top N Percent
                                Jonathan Drummey

                                Hi Sree,


                                You’re welcome… I’d been thinking about writing a longer post on how to be a more effective answerer (while growing your own knowledge at the same time), so this served as a bit of a practice for me, and helped to remind me about what’s important myself. (And has changed how I’ve written my more recent posts).



                                • 13. Re: Rank by Top N Percent
                                  Ville Tyrväinen

                                  I really like the way how you explain things to others