Forum Discussion

donnie17's avatar
donnie17
Regular Visitor
1 year ago
Solved

How to create a dynamic top N for a matrix with multiple values

Hi eveyone, I was wondering if there was any chance to have a way more dynamic Top N slicer. As you can see, if I select 2, I do see top 2 Sales but i alse all the lines of COGS Is it po...
  • Ritaf1983's avatar
    1 year ago

    Hi donnie17 
    Yes, you can achieve it.
    As a first step create a parameters table for selecting Top n 

    And the 2 measures according to yours, like in my case:

    1.

    TopN country profits =
    VAR SelectedTop = SELECTEDVALUE('Top n'[Top n])
    RETURN
    SWITCH(TRUE(),
        SelectedTop = 0, [total profits (K/$)],
        RANKX (
                ALLSELECTED(  'Customers'[Country] ),
                [Total sales (K/$)]
                    )
                      <= SelectedTop,
            [total profits (K/$)]
    )
    2. 
    TopN country sales =
    VAR SelectedTop = SELECTEDVALUE('Top n'[Top n])
    RETURN
    SWITCH(TRUE(),
        SelectedTop = 0, [total_sales],
        RANKX (
                ALLSELECTED(  'Customers'[Country] ),
                [Total sales (K/$)]
                    )
                      <= SelectedTop,
            [Total sales (K/$)]
    )
    Result :

    The pbix is attached

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

  • shafiz_p's avatar
    1 year ago

    Hi donnie17  You already done all the work. You just need to do a small thing.  Select the matrix visual, go to filter pane, Select Total Sales and set to "Is not blank", Click apply. You are done.

     

    I have tried to implement your scenario. See images:

    No filter applied:

    After applied filter:

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution!!

     

     

    Best Regards,
    Shahariar Hafiz