4 Replies Latest reply on Jan 20, 2017 2:33 PM by Jamieson Christian

    Counting Top N using data field from secondary data source

    Nakul Bhadoria

      Hey Tableau Experts, I am facing a issue in my current dashboard. I want to calculate Top N Tenant (Attached twbx file) but I need to multiple my revenue metric with a % metric from secondary data source, I want to calculate Top N using the new metric (Revenue Share). I am unable to find the right way to do that.

       

      Top N will be tied to a parameter.

       

      Structure of the final output will be same as I put in my twbx file.

       

      Please help...its proving to be a show stopper for me

       

      Jonathan Drummey

        • 1. Re: Counting Top N using data field from secondary data source
          Jamieson Christian

          Nakul,

           

          Attached is my sample solution. The workbook is in version 10.1.3, so you may not be able to open it.

           

          Here is what I did:

           

          1. Create a table calc [Revenue by tenant] to sum up the total revenue by tenant. (Make sure the "Compute Along" is set to all fields except [Tenant Name].)

           

          2. Create a table calc [Tenant rank] to rank the total revenues. Configure it to compute along all fields, but at the level of [Tenant Name].

           

          3. Create a field [In Top N] to compare against your parameter and act as your filter. (I created the parameter [Top N], too, since your sample did not have one.)

           

          4. Place [In Top N] on your Filters shelf, and filter on True.

           

          5. Voila!

          • 2. Re: Counting Top N using data field from secondary data source
            Jamieson Christian

            So… the above is not quite there. For some reason, occasionally individual rows for a tenant will carry a different RANK() than the other rows for that tenant. For instance, "Michells" has one row that shows rank 4 rather than rank 5. And Wallmart has a row that shows rank 5 instead of rank 6 — so it's the only row that shows on a "Top 5" filter.

             

            I'm not exactly sure what's going on there, and I couldn't figure out how to reconfigure the table calc to compensate. I'm going to let me subconscious mull over it and see if something comes to mind.

            • 3. Re: Counting Top N using data field from secondary data source
              Nakul Bhadoria

              Thanks Jamie for the reply, please see if you can find some thing which can solve the issue.

               

              Really appreciate.

              • 4. Re: Counting Top N using data field from secondary data source
                Jamieson Christian

                Attached is a revised workbook. I finally got it working, but unfortunately, I ventured into the realm of, "I don't exactly know what I did or why I finally got it working, but it's working — everybody hold your breath".

                 

                I'm not going to do an in-depth, but here are a few highlights of what I changed:

                 

                • I used RANK_DENSE() instead of RANK() so that I could simplify the table calc's "compute along" to a straight Table(down), while still coming up with a single number per Tenant. This fixed the problem with some tenants having a single row whose rank varied from all the rest of the rows.
                • In my filter [In Top N], I had to reference [Tenant Rank] with a LOOKUP(). I'm not exactly sure why. But if I didn't use the LOOKUP(), [Tenant Rank] seemed to evaluate differently in the [In Top N] filter than it did when I was monitoring it in the Measure Values shelf.

                 

                Please review the attached workbook, paying special attention to the "Compute Along" configuration for each table calc.