5 Replies Latest reply on Dec 20, 2016 7:50 PM by K P

# Select the top 3 from second best category

Hello,

My current data looks like this..

Users want to see top 3 sub-categories by sales within second best category by sales.

so in this case I should show only chairs,tables and book cases because those three are top subcategory within second best category which is Furniture.

How do I achieve this?

Thank you.

• ###### 1. Re: Select the top 3 from second best category

KP

Here is my approach.

[Sales by Prod]  // Getting sales by Prod

{fixed [Product Category]:sum([Sales])}

[Rank2] // Getting rank of Prod

RANK_DENSE(sum([Sales by Prod]))

[Rank]  // Getting Rank of sub category

RANK_UNIQUE(sum([Sales]),'desc')

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Select the top 3 from second best category

Shinichiro Murakami, thanks a lot...

appriciate it.

• ###### 3. Re: Select the top 3 from second best category

You are very welcome.

Thanks,

Shin

• ###### 4. Re: Select the top 3 from second best category

Hello K P,

Alternate approach:

I believe you can solve this problem using either Table Calculations or Level of Detail Calculations.  This particular solution uses both.

The first LoD Expression isolates the Category sales so that we can rank them:

{ FIXED [Category] : SUM([Sales]) }

You can then rank these values; make sure to use RANK_DENSE so that there aren't any gaps in the ranking:

RANK_DENSE(min({ FIXED [Category] : SUM([Sales]) }))

Since we are specifically looking for the second highest selling category we can identify that by adding a logic portion to the calculation:

RANK_DENSE(min({ FIXED [Category] : SUM([Sales]) })) = 2

The second calculation identifies the top 3 selling subcategories:

RANK(SUM([Sales])) <= 3

Finally we combined these two calculations in one final calculated field [composite rank].  Please see the attached workbook for this calculation in action.  Note, the Category Rank calculation should be computed using Category and Sub-Category and the Sub-Category rank should be computed using Sub-Category.

Hope this helps!

1 of 1 people found this helpful
• ###### 5. Re: Select the top 3 from second best category

Thank you.

This also helped.