Forum Discussion

yashwant101's avatar
yashwant101
Helper III
3 years ago

Filter Top N on a matrix based on different columns

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

 

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      yashwant101
      Helper III
      3 years ago

      Hi 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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)
                )
            )
        )