How to determine N~Worst Performers or BottomN?

Melissa · September 8, 2018, 9:22am

Liked this topic: Create Automated Lists Based On Ranking Calculations in Power BI

I’m working on a report now where I don’t only want to list N~Best Performers but also list N~Worst Performers. Could be Customers, Salespersons, Products or Regions for a selected Measure. But that is proving to be a challenge…
All related topics I could find were about Segmenting or only TopN, but nothing on BottomN - if you like.
Is there content that covers this? Can anyone point me in the right direction on how to list N~Worst Performers?

sam.mckay · September 10, 2018, 12:51am

Hi Melissa,

That’s great.

This is very very easy with TOPN or alternatively RANKX

The key is to change the from DESC to ASC in the last parameter.

This will mean that the lowest result will be 1.

All the same techniques can then be utilized, nothing really it different.

See how you go with these ideas.

Chrs

Melissa · September 10, 2018, 12:47pm

That Obvious - hope we all get one pass

I tried that and couldn’t get it to work. Today I traced my steps and believe the underlying problem is Filtering. Let’s see if I can describe what I think is happening.

Low 3 SP = 
VAR rankSP = RANKX(ALL('Customer'[SalespersonCode])| [Selected Measure] | | ASC)

RETURN
IF( ISFILTERED( 'Customer'[SalespersonCode] ) |
IF( rankSP <= 3 | [Selected Measure] | BLANK()) |
CALCULATE( [Selected Measure] | TOPN( 3 | VALUES( 'Customer'[SalespersonCode] ) | [Selected Measure] | ASC)))

The [Selected Measure] is driven by a Report slicer AND the same is true for the ‘Customer’[SalespersonCode] this is filtered by a Report slicer on [Company name]. But when the VAR rankSP is evaluated it seems to ignore this Company Filter and result in a list of several BLANKS with a Ranking = 1.

How do I get RANKX to respect the Company filter in the report slicer and/or ignore all BLANKS?
Thanks again.

sam.mckay · September 11, 2018, 11:20am

Would it be possible to see the model on this one? If you can add it here that would be great.

The key to respect all possible filter with RANKX is to change the shape of the table, which is the first parameter.

So instead of ALL( Salespersoncode…), it would need to be something different to contend with additional filters.

These can sometimes be difficult to definitively say without seeing and maybe testing a few things.

Let me know.

Thanks

Melissa · September 12, 2018, 6:16am

That would be great.

After I’ve made some changes for readebility because it’s mainly in Dutch at the moment. You’ll receive it via email (sorry I’m not allowed to publish the file to the forum).

Thank you Sam

sam.mckay · September 12, 2018, 11:58pm

Ok best then is to create an example file with the data masked.

Or just set up a clear example of exactly the same scenario.

Everything is public with the forum so that would be the best course of action here. I will be posting any solution on here.

I’m still just finding it difficult to understand the full scenario. I’ve read through the initial post a number of times and still not too sure of the exact scenario, so as I mentioned I will require something to test, or I need a much clear picture of everything that is going into this one.

Thanks

Melissa · September 13, 2018, 10:39am

Hi Sam,

Attached the sample file. As you can see the TopN works fine (and respects slicers)
But when the VAR rankSP is evaluated for the BottomN it seems to ignore the Company Filter and result in a list of several BLANKS with a Ranking = 1.

I’ve been unable to fix this…

Bottom N Salesperson model.pbix (2.0 MB)

sam.mckay · September 13, 2018, 10:56pm

Yes this is an interesting one. It’s one of the many naunces to the RANKX function.

It’s one I actually covered a bit in a session during the latest learning summit.

See here (around the 18min mark)

What you need to do it make sure that of the salespeople with no values are excluded from the ranking. That is what is causing the problem here

So the RANKX function needs to be dynamic. By this I mean the virtual table that it iterates through.

See here

Low Rank = 
IF( ISBLANK( [Values Q] ), BLANK(),
    RANKX(
        FILTER( ALL('Customer DIM'[Salesperson]), NOT( ISBLANK( [Values Q] ) ) ),
            [Values Q] , , ASC))

This is totally dynamic now for any selection that you make.

Attached.

Bottom N Salesperson model.pbix (2.0 MB)

Melissa · September 14, 2018, 6:06am

Thank you Sam.
I knew the problem was Filtering but I couldn’t work out a solution - now I’ll never forget!

All the best.
Melissa