Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
yashwant101
Helper I
Helper I

Filter Top N on a matrix based on different columns

Hi,

 

I have a requirement on a matrix similar to below one:

yashwant101_2-1679241489323.png

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.

yashwant101_3-1679241724053.png

I tried doing it with RANKX, but I am not able to do it. Any help would be appreciated.

 

Regards

 

 

4 REPLIES 4
ConnorSSM
Frequent Visitor

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. 

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

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.