9 Replies Latest reply on Aug 2, 2013 5:15 AM by Jim Wahl

Top N % pie chart plot

Hello ,

I am trying to achieve the following :

I have one table having the following columns : Dealer_Name , Date_Time , Recharge_Count , Recharge_Value.

Need to plot a pie chart for TOP N % Dealer's against Recharge_Count . I have managed to do for TOP N Dealer's but for TOP N % Dealer's facing a tough time.

Please give some guidance on this.

Regards,

Suhrid R. Ghosh

• 1. Re: Top N % pie chart plot

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

1 of 1 people found this helpful
• 2. Re: Re: Top N % pie chart plot

Hello Brad ,

Thanks for you reply. Your blog really helped me plotting my scatter plot for Top N % Dealer . However for pie chart i din got any much help but i have figured it out by myself . Here is the sample worksheet where i have done the Top N % Dealer pie chart plotting.

Thanks & Regards,

Suhrid R. Ghosh

• 3. Re: Re: Top N % pie chart plot

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

1 of 1 people found this helpful
• 4. Re: Re: Re: Top N % pie chart plot

Jim really a load of thanks for you valuable time and expertise suggestions .

Regards,

Suhrid R. Ghosh

• 5. Re: Re: Re: Top N % pie chart plot

Hi Jim,

Got stuck on one more thing that i am trying to achieve in the same attached workbook but facing a tough time. I want to show percentage of each slice in the tool tip. How can this be achieved. Can you please look on to this.

Thanks & Regards,

Suhrid R. Ghosh

• 6. Re: Top N % pie chart plot

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

• 7. Re: Re: Top N % pie chart plot

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

• 8. Re: Re: Re: Top N % pie chart plot

Jim , i couldn't have done this with out you. Excellent !!! . Thanks for posting the result and the kind of solution you gave , simply loved it !!!

once again thanks a lot  ...

Regards,

Suhrid R. Ghosh

• 9. Re: Re: Re: Top N % pie chart plot

No problem . Have a great weekend.

Jim