Complex RANKX with multiple slicers/filters

[Sjoerd_g]

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 than 3m rows for 4 months of data. The dataset is for responses to a survey and contains responses from many different companies and branches, which can sit within 19 different segments (at the branch level).

I want to achieve a few things:

Show table with company ranking sliced by Segment and Date, and on top of that, we need to be able to exclude companies with low responses. This may be hardcoded in the DAX, but if possible, with slicer/filter, even better.

Secondly, a table that shows the company's rank within a segment, the number of companies within the segment, and the maximum and minimum scores within the segment. -- I have not even started solving this problem yet as I need to have the NPS ranking first, but this is the ultimate goal.

I tried many different combinations with RANKX, firstly the straightforward:

RANKX(ALLSELECTED(sample3),

[NPS score])

OR:

CALCULATE(

RANKX(sample3,

[NPS score]),

ALLSELECTED(sample3[Benchmarking]),

ALLSELECTED(sample3[visit date (bins)])

)

But the rankings are all over the place (except when the score is 100), and I just can't figure out how to properly write it, so it takes everything into account. The things that make it more complex are:

• In the dataset, there are entries without a nps_score as there are several channels, and not all are survey responses. In the [NPS Calc] measure, I accounted for this by filtering "nps_score <> BLANK()" and added it as a filter on the visual.
• Branches within a Company can sit in different Segments, so the Company NPS can be different per Segment.
• The outside filters need to be accounted for, most importantly, Date and Segment.
• Companies with low responses should be excluded from the ranking (I.e. [Response #] >= 10).
  • To accomplish this I was thinking of an IF statement like:
  • IF(

    CALCULATE(

    DISTINCTCOUNT(Sample3[response id]),

    FILTER(Sample3, DISTINCTCOUNT(Sample3[response id]) >= 10)) = BLANK(),

    "", ...

The sample file is available here: https://we.tl/t-jo210iYADN

I would be grateful if someone could point me in the right direction as I have been stuck for a while and getting more confused by the minute. Thanks!

[davehus]

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]

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]

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]

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.

[v-janeyg-msft · Accepted solution]

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

[Sjoerd_g]

I have implemented it into my model and works beautifully. Thank you very much Janey!

[Sjoerd_g]

Hi Janey,

Thank you so much for looking into this and providing me with your solution. I'm having a bit of a crazy week but will try to implement this as soon as I can and get back to you.

Oh, yes sorry that the sample file is a bit of a mess. I promise the actual dataset and report is much better. 😉

Thanks!