Forum Discussion

yashwant101's avatar
yashwant101
Icon for Helper III rankHelper 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 ano...
Need Help
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)
        )
    )
)
yashwant101's avatar
yashwant101
Icon for Helper III rankHelper III
3 years ago

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.