Forum Discussion

Sjoerd_g's avatar
Sjoerd_g
Frequent Visitor
4 years ago
Solved

Complex RANKX with multiple slicers/filters

Hi all,   I am stuck on a RANKX DAX formula and getting very strange results. There are multiple things at play that make the RANKX a bit complex. I created a sample file, but the database has more...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, Sjoerd_g 

     

    Your model is messy and so are the requirements, it's hard for me to give specific opinions.

    For your idea, the reference I can give is that you need to use summarize() in rank() function to customize a context you want, and then sort it. 

    If you don't want the line with response < 15 to appear on the visual, you can filter in the filter pane like this:

    Measure =
RANKX (
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( sample3 ),
            [Benchmarking],
            [company],
            "Responses", DISTINCTCOUNT ( sample3[response id] ),
            "NPSSOCRE", CALCULATE ( AVERAGE ( sample3[NPS Calc] ), sample3[nps_score] <> BLANK () )
        ),
        [Responses] > 15
    ),
    [NPS score],
    ,
    ,
    DENSE
)

     

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

    Best Regards,
    Community Support Team _ Janey

     

davehus's avatar
davehus
Memorable Member
4 years ago

Hi Sjoerd_g ,

 

You will get rank by segment using the measure below. 

 

Rank By Segment = RANKX(ALL(sample3[company]), [NPS score])
 
In terms of the overall company rank, am I right in saying that you want to get a rank of the total nps score across all benchmarks for a company?
 
  • Sjoerd_g's avatar
    Sjoerd_g
    Frequent Visitor
    4 years ago

    Hey Dave, thanks for this. This does indeed work in a way but it won't account for the number of responses.

     

    I tried to get around this by an IF statement but then it just replaces the rank with a blank:

    Rank By Segment =
    IF(
    CALCULATE(
    DISTINCTCOUNT(Sample3[response id]),
    FILTER(Sample3, DISTINCTCOUNT(Sample3[response id]) <= 15)),
    "",
    RANKX(
    ALL(sample3[company]),
    [NPS score]))

    Nor does:

     

    Rank By Segment =
    CALCULATE(
    RANKX(
    ALL(sample3[company]),
    [NPS score]),
    FILTER(sample3,DISTINCTCOUNT(sample3[response id]) >= 15))

     

    • davehus's avatar
      davehus
      Memorable Member
      4 years ago

      Hi Sjoerd_g, we can work on that bit.

      A calculated column with an allexcept will sum total responses and you can use it as a slicer or in the measure. Just need to test. ğŸ˜Š Just in terms of the company, is it based on total nps. Send me desired result in an excel doc. Even a snip will do. 

      • Sjoerd_g's avatar
        Sjoerd_g
        Frequent Visitor
        4 years ago

        Cool thanks davehus 

         

        Yes, the NPS is calculated at the company level (but can differ per segment because not all scores for a company will sit in the same segment).

         

        What I would expect to see if we slice by Segm3 and filter out responses < 15 is for company BB to be ranked 1 (and the only company in this sample file)

         

        Or if we slice by Segm2 and filter responses <= 15 then we would see company BB and CC on a shared first place:

         

        I tried the ALLEXCEPT but could not get the DISTINCTCOUNT in there (or [Response #] measure)

         

        Ps. keeping in mind the Response count is further filtered by Channel as not all are NPS responses. In the sample file the Channel = BLANK. For instance, in Segm3 we will have company AA with 13 responses when filtered by Channel = BLANK, but 14 responses if we do not take this into account, so if we would have [responses #] < 14 it would still show as:

        But then if we apply the Channel filter is blank then it should result in BB being 1st. 

         

        I'm sorry if I'm not making any sense... This one is doing my head in haha.