Filter Top N on a matrix based on different columns

[yashwant101]

Hi,

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.

Regards

[ConnorSSM]

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. 

[yashwant101]

Hi @ConnorSSM,

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

[ConnorSSM]

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.