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 possible to have the same number of lines for COGS (in this case) as there are for Total Sales? For example, if I select 3, only 3 rows should appear, and so on.

Here DAX for TotalSales

TotalSales =
VAR vSegmentRanking = RANKX(ALLSELECTED(financials[Segment]),[Sales],,DESC,Dense)
VAR vCountryRanking = RANKX(ALLSELECTED(financials[Country]),[Sales],,DESC,Dense)

VAR vResults =
    SWITCH(
        TRUE(),
                ISINSCOPE(financials[Segment]),vSegmentRanking,
                ISINSCOPE(financials[Country]),vCountryRanking
    )
RETURN
        IF(vResults <= DynamicRank[Valore DynamicRank], [Sales])



Need Help
Tips and Tricks
  • Ritaf1983's avatar
    Ritaf1983
    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
    shafiz_p
    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

3 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    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
    shafiz_p
    Icon for Super User rankSuper User
    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