
1. Re: Select the top 3 from second best category
Shinichiro Murakami Dec 20, 2016 3:04 PM (in response to K P)1 of 1 people found this helpfulKP
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

2. Re: Select the top 3 from second best category
K P Dec 20, 2016 3:13 PM (in response to Shinichiro Murakami)Shinichiro Murakami, thanks a lot...
appriciate it.

3. Re: Select the top 3 from second best category
Shinichiro Murakami Dec 20, 2016 3:15 PM (in response to K P)You are very welcome.
Thanks,
Shin

4. Re: Select the top 3 from second best category
Madeleine Corneli Dec 20, 2016 3:26 PM (in response to K P)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 SubCategory and the SubCategory rank should be computed using SubCategory.
Hope this helps!

223191_forum_response.twbx 1.1 MB


5. Re: Select the top 3 from second best category
K P Dec 20, 2016 7:50 PM (in response to Madeleine Corneli)