Dynamic TopN Filter with multiple column

I am working on a project. I need to create a dynamic top n which should work when different slicers are used. I used RankX which gives me the results i need when i group on only one column, eg clien...

v-jiascu-msft

8 years ago

Hi aaarmstee67,

Seems your conditions here are different from your previous sample. But you can adjust it to satisfy your requirements. I have sent the demo base on your data through Private Message.

selectedTopN =
VAR ranks =
    CALCULATE (
        RANKX ( ALL ( 'TESTPBI1'[contactname] ), CALCULATE ( SUM ( TESTPBI1[qty] ) ) ),
        ALLSELECTED ( TESTPBI1[companyname] ),
        ALLSELECTED ( TESTPBI1[country] ),
        ALLSELECTED ( TESTPBI1[Value] )
    )
RETURN
    IF (
        ISBLANK ( 'TopN'[TopN Value] ),
        ranks,
        IF ( ranks <= 'TopN'[TopN Value], ranks, BLANK () )
    )

Finally, you can select the top N value from the slicer and filter out the blanks to show top N only.

Best Regards,

Dale

aaarmstee67

Helper I

8 years ago

IThanks for the response. But it looks as if it is not working at my end. Can you take a look please.

https://1drv.ms/u/s!AvCnR2EyBsfsaVoZkJ54-5WhjPQ

v-jiascu-msft

Microsoft Employee

8 years ago

Hi aaarmstee67,

Please delete the link if it contains sensitive data.

The trick is the context will be filtered down to one row as a group. So all the rank is 1st because it's 1st in its own group. Please try to clear the context you don't need.

Ranks 2 =
CALCULATE (
    RANKX ( ALL ( TESTPBI1[contactname] ), CALCULATE ( SUM ( TESTPBI1[qty] ) ) ),
    ALL ( TESTPBI1[companyname] )
)

Ranks 3 =
CALCULATE (
    RANKX ( ALL ( TESTPBI1[companyname] ), CALCULATE ( SUM ( TESTPBI1[qty] ) ) ),
    ALL ( TESTPBI1[contactname], TESTPBI1[Value] )
)

Best Regards,

Dale

Forum Discussion

Dynamic TopN Filter with multiple column