Forum Discussion
Helper IIIFilter Top N on a matrix based on different columns
I think you could use a numeric list parmeter and the new WINDOW function to grab the Top N products and leave the metric as a slicer.
CALCULATE(
SUM(TABLE[M1]),
WINDOW(
1,ABS,
[PARAMETER NAME], ABS
ORDERBY(ALLSELECTED(Table[M1]), ASC)
)
WINDOW will create a table grabbing the products at the 1 - [Parameter Name] row and the metric slicer will filter the table accordginly.
Helper IIIHi Anonymous,
Thank you for your response. I tried using it. But the problem is I am not able to put the field parameter name(metric slicer) here.
Moreover, we need top 2 as well as bottom 2 in the same dropdown.
Regards,
Yashwant
Is your parameter a generated list or field value? If you are looking to hard code in only the top 2 then I would suggest dropping the slicer and replace the [Parameter Name] with 2.
Also, you can grab the top and bottom products by using a union statement to join the top and bottom. One other thing, you'll probably need to throw the entire filter expresion inside of KEEPFILTERS()...
CALCULATE(
SUM(TABLE[M1]),
KEEPFILTERS(
UNION(
WINDOW( //Lookuptable grabbing the top products
1,ABS,
[PARAMETER NAME], ABS //IF you only want top 2 then replace parameter w/ 2
ORDERBY(ALLSELECTED(Table[M1]), Desc)
),
Window( //Lookuptable grabbing the bottom products
-2, ABS, //-2 Will grab the 2nd to last value in the table
-1, ABS,
ORDERBY(ALLSELECTED(Table[M1]), Desc)
)
)
)- yashwant101
I need a dropdown where the users can select the metric (inventory, order amount and revenue) based on which the top or bottom rows for the matrix will be filtered.
And it will not have any hardcoded value. The number of top or bottom rows will also be decided based on a numeric range.