12 Replies Latest reply on Nov 3, 2015 2:14 PM by Jack Rogers

# How can I group top 5 selling products for individual regions?

I need to create a dashboard where I display in a regional map the top 5 selling for each region and offer interactivity. But I'm running into so many issues. I initially tried going through products but there are too many extensive products for me to properly look into to group together, is there an easy way to find the top 5 selling products for each region and group them together for each individual region.

• ###### 1. Re: How can I group top 5 selling products for individual regions?

Hi Jack

This is one way... you need v9

This is the formula

RANK_UNIQUE(SUM({FIXED [Region],[Product Name]:SUM([Sales])}))

Edit Calculation is important

Combined view

The rank is calculated at region level you can then filter for top 5.

Rody Zakovich any addtional thoughts/ workarounds/ performance improvements?

Rody is now my go to guy on ranking Multi-Level Sorting with Ranks

9.1.1 version attached

PS. I don't think sets will work as you need to recalculate at region....

Cheers

Mark

• ###### 2. Re: How can I group top 5 selling products for individual regions?

Off the top of my head you'll need to have lat/long for your regions (maybe even create a calc that groups states together for your regions), then create a combined field for region & product to use as a sort, and finally do a top 5 filter on product.

Once you have that info set you should be able to do a filled or symbol map.

• ###### 3. Re: How can I group top 5 selling products for individual regions?

Hey Jack,

The solution that Mark offered will most likely serve your needs. The other option is to use a combined Filed and INDEX(). For example.

Drag Region, Product Name onto the Row Shelf and Sales onto the Column Shelf.

Then create a combined field with Region and Product Name

Drag that field between Region and Product Name on the Row Shelf. And sort that Filed in Desc order by Sum Sales

Unshow Header to clean things up.

Finally create an INDEX() calc and place it on the Filter Shelf. Change the calc so that it address Region and Product Name, and Restart every region.

Then you can change this as needed. From there you can format as you want

Regards,

Rody

• ###### 4. Re: How can I group top 5 selling products for individual regions?

Hey, I've done all you've suggested to do and thank you. But I'm still running into an issue. It's ordered all the products into the top 5 state, but i still have a very large list of the other products I don't need now. Is there a way to get rid of them? I've tried grouping them but that also groups together the fields in the other regions too and that messes it all up.

• ###### 5. Re: How can I group top 5 selling products for individual regions?

Hello Jack,

First create a calculated field, and type in this

INDEX()

Drag that onto the Filter Shelf and just click OK. After, right click on it and go to Edit Table Calculation. On Compute Using, change it to Advanced...finally follow the steps below.

Change the calc so that it address Region and Product Name, and Restart every region.

Regards

Rody

• ###### 6. Re: How can I group top 5 selling products for individual regions?

Thanks, Ive already done all that, it's ordered it all up, but I still have way too much data in terms of products names, i need a means of removing the rest of the product names from the list. I'll evidence in the image below.

• ###### 7. Re: How can I group top 5 selling products for individual regions?

Hey Jack,

You must be missing a step somewhere a long the way.

Can you provide additional screenshots of your viz (Including the Filter shelf) as well as, screens of how you have the INDEX() filter set

Regards,

Rody

• ###### 8. Re: How can I group top 5 selling products for individual regions?

Okay here's some screengrabs i got from my work.

• ###### 9. Re: How can I group top 5 selling products for individual regions?

Just to be sure, you changed the slider to 1 => 5

Regards,

Rody

• ###### 10. Re: How can I group top 5 selling products for individual regions?

I did that, and it did change it, but not in the way I wanted it to, now it seems messed up. There's no even display of the top 5 products compared to my previous display. It all seems to be based on the same product

• ###### 11. Re: How can I group top 5 selling products for individual regions?

Jack.........

I am very sorry, I am the one who miss-stepped here.

I forgot that we cannot use a Tableau Generated Combined Field to do this, we have to create one manually