Suhrid,

There is no built-in functionality for Top N %, you will have to use table calculations to achieve.  I demonstrate the method in the following blog post:

Breaking BI: Top N% Filters in Tableau

Thanks,

Brad Llewellyn

Associate Data Analytics Consultant

Mariner, LLC

brad.llewellyn@mariner-usa.com

http://www.linkedin.com/in/bradllewellyn

http://breaking-bi.blogspot.com

Nice job. Thanks for posting the result.

A couple of minor comments:

• In the In top N%? formula, you can use SIZE() instead of WINDOW_COUNT(MIN([Dealer Name])). It's a bit cleaner and maybe faster.
• The Top N% parameter may look better as a float formatted as a percentage with no decimal.

Then your In Top N% formula is just: INDEX() <= SIZE() * [Top N%]. Which is easy to understand.

Jim

Hi Suhrid,

Isn't it incredible how something so seemingly easy is so difficult.

I think this is possible, but will require a new approach. You can't use measure names / measure values to color / size the pie slices, since formulas can't reference the measure names / value fields.

I'm out of time for today, but will give it a look tomorrow morning (Europe time).

Jim

OK, a little sleep helped.

The key issue here is

1. Top N% requires a table calc---using either the SIZE() or WINDOW_COUNT() approaches.
2. Table calcs are always measures, even they look like dimensions; for example: Name = IF [In Top N%] THEN "Top N" ELSE "Other" END. You can't move Name to the Dimensions area in Tableau.
3. You can only partition table calcs on dimensions. Therefore you can't create a measure WINDOW_SUM(...) partitioned on Name.

There are a couple of workarounds: First is to create individual measures for each partition and use Measure Names / Measure Values in the view. This was your original solution, which I thought worked nicely, but prevents you from doing secondary calculations on the values, since measure names / values can't be referenced in a calculated field.

The second approach is to manually create the partitions. There several variations of this. I'm using the simplest, because your view requirements are simple---just two categories in a pie chart.

Here's how I did it. All of the formulas I created are prefaced with the last letter of the alphabet (if I use that letter here too many times, Tableau's forum software may reject the post as spam).

The key fields are Name and Value. Name is category name:

IF [In top N%]

    THEN "Top N"

    ELSE "Other"

END

Value returns the WINDOW_SUM() for each partition:

IF FIRST() == 0 THEN

    WINDOW_SUM(SUM([RechargeValue (SCR)]), 0, [Z-Top N position]-1)

ELSEIF FIRST() == -([Z-Top N position]) THEN

    WINDOW_SUM(SUM([RechargeValue (SCR)]), 0, SIZE()-[Z-Top N position]-1)

END

Top N position is just SIZE() * Top N%. In your example there are 381 dealers in the view and 38 dealers in the top 10%.

The IF / ELSEIF clauses specify the partitions using the FIRST() function. FIRST() returns the position of the first row relative to the current row. We know that the Top N partition starts at the first row, so we can use a FIRST() ==0 to check for this. And the "Other" partition starts at position 38 (the 39th row, since the index starts at 0). In this case the first row is 38 rows back and FIRST() == -38.

Next the WINDOW_SUM functions aggregate the values within each partition. The key is the start, end values in WINDOW_SUM(..., start, end). For the Top N partition we want the first 38 values; starting at the current row (start = 0) and ending 38 rows later (end = 37). For the Other partition, we again start at the current row (start = 0) and want the remaining 381-38 = 343 values, which is SIZE() - Top N position.

Lastly I created a Value % field for your tooltip or label:

     [Z-Value] / TOTAL(SUM([RechargeValue (SCR)]))

Here's the new result is on the right below, next to your original measure names/values approach.

I left a "Work" worksheet that shows the values FIRST(), INDEX(), Top N, SIZE() ... values and may help you verify and understand the solution.

Jim

No problem . Have a great weekend.

Jim