Forum Discussion

Dylan24's avatar
Dylan24
Frequent Visitor
1 year ago
Solved

Top N Slicer PowerBi

Hi, 
Can anyone assist please. 
I have created a TopNParameter table TopNParameter = GENERATESERIES(10, 99.9, 10)

The table I want to slice has a column called "Attendance%" and has values from 0% to 100%
There is also a column called adhereancevsnonAdhereance which I have added as a visual filter and ticked non-adhereance.

I want to rank staff attendance%, so when 10 is selected staff with percentages 0% to 25% are shown for the period selected...and so on. 
My issue is that I cannot get the relationship formed between the table source and the TopNParameter table. 

I have seem and many options on forums such as this but juts cannot get the synergy correct. 

Any ideas out there please would be welcomed?
 

  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago

    Hi Dylan24 

     

    If you create a measure like this:
    Filter measure =
    IF(
    RANKX(ALL(Query1), Calculate(Sum(Query1[AdherenceNo%])), ,ASC) <= [TopN Value]
    ,1)

     

    Then add this new measure into your filter pane and filter to is 1.

     

3 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    1 year ago

    Hi Dylan24  

    Could you take a screenshot of what you are trying to achieve.

    I feel like this is similar, a table with Staff, Att % and a slider controlling which to show.

     

    • Dylan24's avatar
      Dylan24
      Frequent Visitor
      1 year ago

      Many Thanks Sam

      In query editor I have created a custom column call AdherenceNo%, which takes Aherence% and *100. This is to avoid changing Adherence% later in table view to a whole number and it eliminates the % sign.

      Below you will see the tiles which are based on the topN slicer. At the end I will paste all the additional items that I have created. At this stage when I click on the 10 tile I want to see the top 10 employees that have not adhered to a working model.


      Parameter Table

      TopN = GENERATESERIES(10, 100, 10)

      Custom Column
      RankMeasure = RANKX(ALL(Query1), [AdherenceNo%], ,DESC)

      Custom Column (when I try create this as a measure it regects the request)
      TopNMeasure = IF(
          Query1[RankMeasure]  <= SELECTEDVALUE('TopN'[TopN]),
          Query1[AdherenceNo%],
          BLANK()
      )

      Any assistance would be much appreciated.
      • SamWiseOwl's avatar
        SamWiseOwl
        Super User
        1 year ago

        Hi Dylan24 

         

        If you create a measure like this:
        Filter measure =
        IF(
        RANKX(ALL(Query1), Calculate(Sum(Query1[AdherenceNo%])), ,ASC) <= [TopN Value]
        ,1)

         

        Then add this new measure into your filter pane and filter to is 1.