Dynamic TopN Filter with multiple column

[aaarmstee67]

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 clientgroup, but the issue arises when i add different columns, such as ClientGroup, SalesLocation, SalesPersonName, HoursWorked. The results is not right when i apply different slicers.  I tried to create a column for rownumber so that i can get the dynamic topn, that is if the row number can default to start from 1 with increment of 1 whenever any filter is applied. But this is becoming more difficult than i thought. Can anybody help. Urgent please. I have spent several days on this but to no avail.

[v-jiascu-msft]

Hi @aaarmstee67,

What's your expected result? Please try out the demo in the attachment.

Best Regards,

Dale

[aaarmstee67]

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]

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

[aaarmstee67]

Thanks for the efforts and the prompt response. I think am not doing a good job explaining what am working on.

The aim of the project is to give users the flexibility to select TOP N

.

Table: It has three columns from the dimension table: ClientGroup, Department, SalesPerson

Measure: HoursWorked

Slicers: SalesPersonRole (PrimaryRole, SecondaryRole and TertiaryRole)

          :Period (Year - 2013 to 2018)

          :Office (Several)

          : Unit(Several eg. Marketing etc)

         :SalesPerson(Names)

        : TopN Slicer

Users need to be able to apply any of these filters and still get the required TopN based on the ClientGroup, as well as the different departmens, and Salespersons, and their hours work within only the TopN. Eg. if usesr want to know the topn clients of a Salesperson within a certain year they should be able to get that etc. 

The inbuilt Top N function is able to do that but they don't want a situation where they have to change it all the time using the visual filters. Please help. 

[aaarmstee67]

Thank for the efforts and the prompt response. I think am not doing a good job explaining what am working on.

The aim of the project is to give users the flexibility to select TOP N

.

Table: It has three columns from the dimension table: ClientGroup, Department, SalesPerson

Measure: HoursWorked

Slicers: SalesPersonRole (PrimaryRole, SecondaryRole and TertiaryRole)

          😛eriod (Year - 2013 to 2018)

          😮ffice (Several)

          : Unit(Several eg. Marketing etc)

         :SalesPerson(Names)

        : TopN Slicer

Users need to be able to apply any of these filters and still get the required TopN based on the ClientGroup, as well as the different departmens, and Salespersons, and their hours work within only the TopN. Eg. if usesr want to know the topn clients of a Salesperson within a certain year they should be able to get that etc. 

The inbuilt Top N function is able to do that but they don't want a situation where they have to change it all the time using the visual filters. Please help. 

[aaarmstee67]

Thank for the efforts and the prompt response. I think am not doing a good job explaining what am working on.

The aim of the project is to give users the flexibility to select TOP N

.

Table: It has three columns from the dimension table: ClientGroup, Department, SalesPerson

Measure: HoursWorked

Slicers: SalesPersonRole (PrimaryRole, SecondaryRole and TertiaryRole),Period (Year - 2013 to 2018),Office (Several),Unit(Several eg. Marketing etc),SalesPerson(Names),TopN Slicer

Users need to be able to apply any of these filters and still get the required TopN based on the ClientGroup, as well as the different departmens, and Salespersons, and their hours work within only the TopN. Eg. if usesr want to know the topn clients of a Salesperson within a certain year they should be able to get that etc. 

The inbuilt Top N function is able to do that but they don't want a situation where they have to change it all the time using the visual filters. Please help. 

[v-jiascu-msft · Accepted solution]

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]

Thanks soooo much @v-jiascu-msft.