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

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

      Dynamic_Top_N_Filter_with_multiple_column2

       

      Best Regards,

      Dale

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

        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.