Forum Discussion

aaarmstee67's avatar
aaarmstee67
Icon for Helper I rankHelper I
8 years ago
Solved

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...
Need Help
  • v-jiascu-msft's avatar
    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.

    topn2

     

     

    Best Regards,

    Dale

aaarmstee67's avatar
aaarmstee67
Icon for Helper I rankHelper I
8 years ago

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's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
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.

topn2

 

 

Best Regards,

Dale