Forum Discussion

Stemar_Aubert's avatar
Stemar_Aubert
Resolver I
6 years ago
Solved

Filter a RANKX result on a specific visual

Hello and happy new year.

 

I have a RANKX formula that gives out the rank of sales people based on specific KPIs.

A slicer on a page changes the KPI on which sales people are ranked.

 

 

IF (
            SELECTEDVALUE ( RankMethod[Flag] ) = 1,
            RANKX ( ALLNOBLANKROW ( [SALES REP] ), [KPI_0] ),
            IF (
                SELECTEDVALUE ( RankMethod[Flag] ) = 2,
                RANKX ( ALLNOBLANKROW ( [SALES REP] ), [KPI_1] ),
                IF (
                    SELECTEDVALUE ( RankMethod[Flag] ) = 3,
                    RANKX (
                        ALLNOBLANKROW ( [SALES REP] ),[KPI_2] ),
                    RANKX (
                        ALLNOBLANKROW ( [SALES REP] ),[KPI_3]
                    )

 

 

This is an extract of my formula (RankAll). It works for what I need, however I there is an issue with a specific visual.

 

 When I put the KPI_0 and another measure (KPI_0 vs Target) to build the Measure Absolute and Measure Deviation in the custom Scroller visual, I can't use my RANKX formula to filter on a specific Sales individual.

 

When looking at the scroller for the #2 individual, applying RankAll = 2 in PBI filters doesn't work. This might be caused by the fact that I use a product category in this visual, which breaks my Ranking Formula.

 

Is there a way to write the RANKX formula to work when I apply categories, or filter for only a specific Sales individual ?

 

I have tried multiple ways, including a formula that gives out the name (string) of the #x individual :

 
Rep 2 = FILTER(VALUES([SALES REP]),[RankAll] =2)
 
However, it doesn't work.
 
What works, albeit not well, is when I put the SALES REP in the visual's filter field and then apply a BOTTOM N + RankAll. However, that creates another issue since it only works for the BOTTOM 1, as BOTTOM 2 takes two individual worth of data, BOTTOM 3 takes three, etc.
 
Thanks for your ideas !

 

 

 

Need Help
Tutorial Requests
  • Stemar_Aubert's avatar
    Stemar_Aubert
    6 years ago

    Ok so I solved it.

     

    I can't pass the rankx formula in the filter fields, as it breaks. I couldn't also use it to generate a new DAX table, because I have report-wide filters, and it would not capture them.

     

    So what I did was, create 3 measures that yields the name of Sales individual 1,2 and 3 each. Then pass the Sales individual name as filter on visual, select TOP n of this measure. Works perfectly.

     

    Not pretty, but I got what I needed.

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Stemar_Aubert how your tables are connected? Seems like you have to remove filter from product category. To simply test it, put a table visual, add sales person and product category and rank measure in it and see if your rank is showing expected result, if not that is your problem and need to be fixed.

    • Stemar_Aubert's avatar
      Stemar_Aubert
      Resolver I
      6 years ago

      Hello parry2k ,

       

      You are correct. The table doesn't yield what I need and product categories mess up my RankAll formula.

       

      I created another Rank formula which "holds", meaning it will always give the same rank to an individual, even on a table showing product categories.

       

       

      TEST =
CALCULATE (
    RANKX (
        ALLNOBLANKROW ( Reps[INDUSTRY REP] ),
        [KPI_4]
    ),
    ALLSELECTED ( Machine[PRODUCT FAMILY] )
)

      However it still doesn't work when I use it as a filter in the custom visual and seems broken. Filtering on "TEST is 2" for exemple, the visual displays nothing at all.

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        Stemar_Aubert IN which visual it is not working? How you are filtering it? Can you share that info please?