9 Replies Latest reply on May 16, 2018 9:44 PM by Chloe S

# Buckets wise sum for Products ( Rank | Index | Bins )

Hi Tableau Lovers,

I am looking for way to get the below output in Tableau.

Expected Output

(Values of Sales will not match, its just a for reference)

Here, the moment I remove the Product name , I am  not getting the desired the output.

I have used this 2 options. But since they are tableau calculation they are not working.

Option 1 - Index

INDEX()

Option 1 - Naming

IF  [Option 1 - Index]<=10 THEN "Top N"

ELSE IF  [Option 1 - Index]>=11 and [Option 1 - Index]<=20 then "11-20"

ELSE IF  [Option 1 - Index]>=21 and [Option 1 - Index]<=30 then "21-30"

ELSE IF  [Option 1 - Index]>=31 and [Option 1 - Index]<=40 then "31-40"

ELSE IF  [Option 1 - Index]>=41 and [Option 1 - Index]<=50 then "41-50"

else  "Others"

end

end

END

END

END

Option 2 - Rank

RANK_UNIQUE(sum([Sales]))

Option 2  - Naming

IF  [Option 2 - Rank]<=10 THEN "Top N"

ELSE IF  [Option 2 - Rank]>=11 and [Option 2 - Rank]<=20 then "11-20"

ELSE IF  [Option 2 - Rank]>=21 and [Option 2 - Rank]<=30 then "21-30"

ELSE IF  [Option 2 - Rank]>=31 and [Option 2 - Rank]<=40 then "31-40"

ELSE IF  [Option 2 - Rank]>=41 and [Option 2 - Rank]<=50 then "41-50"

else  "Others"

end

end

END

END

END

Please let me know, how should i get it done.

Regards,

Sumeet

• ###### 1. Re: Buckets wise sum for Products ( Rank | Index | Bins )

Hi, Sumeet

Basically, you need 3 calculation fields, the sample below I just use the same logic you used up to the 41-50 group and put everything else as other, you can expand to any level as you need.

Hope this could help

ZZ

2 of 2 people found this helpful
• ###### 2. Re: Buckets wise sum for Products ( Rank | Index | Bins )

Hi

Zhouyi Zhang

This exactly what I was looking for....

I never thought of using % mod live ... This is truly new learning for me.

However it looks like I am missing something.

Could you please help me with workbook or correct if I am doing anything wrong...Value are incorrect in my result

Regards,

Sumeet

• ###### 3. Re: Buckets wise sum for Products ( Rank | Index | Bins )

Hi, Sumeet

Please find a sample workbook in 10.0.

ZZ

1 of 1 people found this helpful
• ###### 4. Re: Buckets wise sum for Products ( Rank | Index | Bins )

Thank you so much for this lesson.

Works perfectly and this is exactly what I was hoping to learn.  Thank you

• ###### 5. Re: Buckets wise sum for Products ( Rank | Index | Bins )

enjoy Tableau

ZZ

• ###### 6. Re: Buckets wise sum for Products ( Rank | Index | Bins )

Hi ZZ,

The solution you posted is exactly what I want.  However can you please add another calculation that does the % of total for each TopN grouping?  Total summation of each Top N group divided by the window sum of sales.  Thanks.

• ###### 7. Re: Buckets wise sum for Products ( Rank | Index | Bins )

Hi, Chloe

Let me know if you have question

ZZ

• ###### 8. Re: Buckets wise sum for Products ( Rank | Index | Bins )

Hi,

It worked until I added another dimension which is the 'Qtr'.  The Total sales and % of sales by Rank Group does not work.  I will be uploaded a sample data that is more in line with what I'm working on.

Thanks so much ZZ.  I saw you helped us users with some of the difficult questions.  You're one of the best there!

Best,

On Monday, May 14, 2018, 5:57:08 PM PDT, Zhouyi Zhang <tableaucommunity@tableau.com> wrote:

|

 Tableau Community

|

Buckets wise sum for Products ( Rank | Index | Bins )

reply from Zhouyi Zhang in Forums - View the full discussion

Hi, Chloe

Let me know if you have question

ZZ

 Reply to this message by replying to this email, or go to the message on Tableau Community Start a new discussion in Forums by email or at Tableau Community Following Buckets wise sum for Products ( Rank Index Bins ) in these streams: Inbox

 This email was sent by Tableau Community because you are a registered user. You may unsubscribe instantly from Tableau Community, or adjust email frequency in your email preferences

• ###### 9. Re: Buckets wise sum for Products ( Rank | Index | Bins )

Hi ZZ,

I have the updated Tableau file under this discussion 'Top N with more dimension'.

Thanks much,Chloe

On Monday, May 14, 2018, 5:57:08 PM PDT, Zhouyi Zhang <tableaucommunity@tableau.com> wrote:

|

 Tableau Community

|

Buckets wise sum for Products ( Rank | Index | Bins )

reply from Zhouyi Zhang in Forums - View the full discussion

Hi, Chloe

Let me know if you have question

ZZ

 Reply to this message by replying to this email, or go to the message on Tableau Community Start a new discussion in Forums by email or at Tableau Community Following Buckets wise sum for Products ( Rank Index Bins ) in these streams: Inbox

 This email was sent by Tableau Community because you are a registered user. You may unsubscribe instantly from Tableau Community, or adjust email frequency in your email preferences