Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

default slicer top N value

Hi All,

 

I have a table visual and a slicer to so select the top N value, How can i default the slicer to show top15 please?

 

Thanks 

Ammy

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago
    Hi Ammy,
    We can do the same dynamically, so the user can select top value.
     
    Please follow the following steps:
     
    Create the following measure for calculating the rank:
    Rank By Amount =
    RANKX(ALL('Table_Name'[comp_name]), [amount],,DESC)
     
    Then create a table with one column. (You can use Enter Data option in Power BI)
    For ex use the name Top for both table name and column name.
    Insert values like 10, 20, 30, ....  200.
    Use this table values for filtering dynamically.
     
    Then create the second measure:
    Selected Top N Value = SELECTEDVALUE('Top'[Top])
     
    Then create one more measure measure:
    comp_name Included = IF([Rank By Amount] <= [Selected Top N Value],1,0)
    And add this measure in Filters pane and set the value equal to 1.
     
    Please refer the following image for reference.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hi Ammy,
    We can do the same dynamically, so the user can select top value.
     
    Please follow the following steps:
     
    Create the following measure for calculating the rank:
    Rank By Amount =
    RANKX(ALL('Table_Name'[comp_name]), [amount],,DESC)
     
    Then create a table with one column. (You can use Enter Data option in Power BI)
    For ex use the name Top for both table name and column name.
    Insert values like 10, 20, 30, ....  200.
    Use this table values for filtering dynamically.
     
    Then create the second measure:
    Selected Top N Value = SELECTEDVALUE('Top'[Top])
     
    Then create one more measure measure:
    comp_name Included = IF([Rank By Amount] <= [Selected Top N Value],1,0)
    And add this measure in Filters pane and set the value equal to 1.
     
    Please refer the following image for reference.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi

      In this exmple is it deafulting to top 10 ? Thanks 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        You can add one more measure for default value.

        Selected Top N Value New = IF(HASONEVALUE('Top'[Top]), SELECTEDVALUE('Top'[Top]), MIN('Top'[Top]))
        Also update the previous measure
        comp_name Included = IF([Rank By Amount] <= [Selected Top N Value New],1,0)
  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

     

    To show a slicer with top N, you need to create a Rank Measure like the example below.

    Rank Brand = 
RANKX(
    CALCULATETABLE(
        VALUES( 'Product'[Brand] ), 
        ALLSELECTED()
    ), 
    CALCULATE(
        SUMX(Sales, Sales[Net Price] * Sales[Quantity])
    ),, 
    DESC
)

    and use it as a Visual filter as below

     

    Best Regards,
    Mariusz

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi 

       

      Thanks for your help.

       

      In this case if i use visual filter for the slicer and restict it by setting the  "is less than or equal to" = 15, 

      then the slicer does not allow me to go more than rank 15 , i have upto 200 rank, which the user will also want to see.

      Just deafulting it to the top 15 in the table visual. Please suggest how to achive that.

       

      Thanks  

      • Mariusz's avatar
        Mariusz
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi Anonymous 

         

        Can you share some data sample and explain your scenario based on that.

         

         

        Best Regards,
        Mariusz

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