Top N by Dimension using Set Analysis in Expression

[Anonymous]

I have a need to show top 25 products by Sub Department in my company.

I've seen several descriptions and solutions to this problem, Oleg Troyansky has an excellent write-up on this topic where he shows solutions using Dimension Limiting in Straight tables, Calculated Dimensions in Straight/Pivot tables and expression limiting using set analysis in a Pivot table.

If you haven't read this article, you should - it is an excellent read.

However when he discusses he set analysis version he no longer breaks it out by dimension. Simply top 100 - or 1000 can't remember which overall.

For my needs, assume we have 4 sub departments and I need to show top 25 products in each. This should yield 100 rows.

For all my tinkering, I can only get it to show top 25 over the available set, broken down by sub department. That's 25 Total.

I could probably solve the problem using a calculated dimension or two, but I'm stubborn and am looking for optimal performance. This is my expression (showing top 25 overall):

sum({<MasterPLUCode={"=rank(sum(Amount*NetFlag),4)<=25"}>}Amount*NetFlag)

Any help offered would be hugely appreciated.

[d_pranskus]

Not sure 100%, but it might not work, because you are using two sets with set analysis

So the first expression limits only [Project Name], and if you want filters on other fields you need to repeat them once again. But do not close the set, but continue it

Please try this one

Sum({<[Project Name]={"=AGGR(Rank(Sum({<FunnelStamp2={'Current'} ,SOPCatagory={'Pipeline','Committed','Upside'},SixQtrFunnelFlag={'Yes'},DataType={'6Q'},[Development Stage]={'Obsolete - Diminishing Inventory','Sunset - Pending Obsolescence', 'Intent to Cancel'}>}Amount),1),Region,[Project Name])<=10"}, FunnelStamp2={'Current'} ,SOPCatagory={'Pipeline','Committed','Upside'},SixQtrFunnelFlag={'Yes'},DataType={'6Q'},[Development Stage]={'Obsolete - Diminishing Inventory','Sunset - Pending Obsolescence', 'Intent to Cancel'}>}Amount)/1000000

My suggestion would be to create a variable for the expression and then pass the list of values to it with $ sign expansion.