Top N Slicer in a matrix?

[shubh25]

Hi,

I need this Top N filter/slicer in the form of a slicer in visual so that we are able to see top 5,10 based on the value of balance column and in this matrix form. I am able to do this using RankX in table form but all goes haywire in matrix form.

Is there a solution?

Attaching the link to sample data.

https://www.dropbox.com/sh/u3bmhm9gosvl2te/AACcbXbs-8jWdh_CMgnz5MTSa?dl=0

[Mariusz · Accepted solution]

Hi @shubh25 

Please see the attached solution, I've added an extra table "Top N selector" for filtering and adjusted Rank Balance Measure and Visual filter to reflect the changes.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[shubh25]

Hi @Mariusz,
Thank you very much,
this solved the top N matrix problem I was having.
Just wondering if it also works in another way. e.g., when chosen top 3, it shows only 3 even if 2 or more than 2 source name are selected. 
I tried it by removing the sourcename from the visual and it worked but is there a way that we can have source name as well as only the top 3 throughout the table?

[Mariusz]

Hi @shubh25 
The below will rank top n on Client and Source.Name granularity. 

Rank Balance 2 = 
IF (
    ISINSCOPE( 'TOP N with Matrix'[Client] ),
    INT(
        RANKX (
            CALCULATETABLE (
                GROUPBY('TOP N with Matrix', 'TOP N with Matrix'[Client], 'TOP N with Matrix'[Source.Name] ),
                ALLSELECTED ( 'TOP N with Matrix'[Client], 'TOP N with Matrix'[Source.Name]  )
            ),
            CALCULATE( SUM( 'TOP N with Matrix'[Balance] ) )
        ) <= MAX( 'Top N Selector'[Value] )
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Mariusz]

Hi @shubh25 

what would you expect to see as an outcome, top n clients for each Source.Name?

Best Regards,
Mariusz

Please feel free to connect with me.

[shubh25]

That is right @Mariusz.
e.g., If two source name are being selected, top 3 clients for both of them, so overall 6.
If only one sourcename is selected, then only 3 (belonging to the selected source name)
Basically the result that the slicer from filter pane is achieving.

[Mariusz]

Hi @shubh25 

Please see the below screenshot, is this what you are looking for?

If so please see the attached file and a link to the article below:

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[shubh25]

Hi @Mariusz , 

sorry for not being clear in my original question. 

Basically I want a dynamic slicer with the use of which I can either slide to the number of clients that I want to see or choose whether top 3 or 5 clients get displayed. 
I do not want to use the filter pane at all.

Please find attached the screenshot for your understanding.