Filter Top N on a matrix based on different columns
I have a requirement on a matrix similar to below one:
The requirement is that I need to give two slicers, one to choose based on which metric(inventory, Order Amount, Revenue) and another to choose how many top or bottom values we want to see. The ranking should be for M1 market.
Suppose they select Inventor for metric and top 2 measure they should get top 2 products with most values in the inventory in M1.
I tried doing it with RANKX, but I am not able to do it. Any help would be appreciated.
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) ) ) )