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

[donnie17]

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

[Ritaf1983 · Accepted solution]

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 · Accepted solution]

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

[Ritaf1983 · Accepted solution]

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

[ghoshabhijeet]

@donnie17Thank you for sharing your query. Could you please share the pbix file with some sample data ?