Need a measure that will provide the same output as TopN visual level filter (than I can parameterize it).
The solution for simple cases provided HERE
But it doesn't work for more complicated cases...
EXAMPLE:
Don't work if you add any dimension that has Many to One Product Name
relationship (Order Number
for example).
Desired output: both tables (top and bottom) should be equal:
Example from screen available here HERE
NB! From usability perspective it's preferable to return Sales Rank in measure.
A bit reworked solution from David Bacci:
1. If you need just TopN Sales:
TopnSalesAmount =
VAR param = [TopN Value]
VAR topNTable =
CALCULATETABLE (
TOPN ( param, 'Product', [Sales Amount], ASC ),
ALLSELECTED ( 'Product'[Product Name] ),
FILTER ( ALLSELECTED ( Sales ), [Sales Amount] <> BLANK () )
)
RETURN
IF (
NOT ( ISEMPTY ( Sales ) ),
IF (
SELECTEDVALUE ( 'Product'[Product Name] )
IN SELECTCOLUMNS ( topNTable, "a", 'Product'[Product Name] ),
[Sales Amount]
)
)
2. If you need Rank for TopN Sales:
rnkTopnSalesAmount =
IF (
//ISINSCOPE ( 'Product Names'[Product Name]), -- depends, which one is used in visual
ISINSCOPE ( 'Product'[Product Name] )
&& NOT ( ISEMPTY ( Sales ) ),
VAR ProductsToRank = [TopN Value]
VAR topNTable =
CALCULATETABLE (
TOPN (
ProductsToRank,
ADDCOLUMNS ( VALUES ( 'Product'[Product Name] ), "@Amt", [Sales Amount] ),
[Sales Amount], ASC
),
FILTER ( ALLSELECTED ( Sales ), [Sales Amount] <> BLANK () )
)
RETURN
IF (
SELECTEDVALUE ( 'Product'[Product Name] )
IN SELECTCOLUMNS ( topNTable, "a", 'Product'[Product Name] ),
RANKX ( topNTable, [@Amt], [Sales Amount], ASC )
)
)