Ranking - Only return top 5

DAX
1

Hi,

Sorry for the bad topic name. If anyone has a suggestion for a better one please let me know.

I have been playing around with the DAX Workout 025 - Top and Bottom 5 found here: DAX Workout 025 - Top and Bottom 5

I solved the initial challenge and got the Top and Bottom 5 customers to work, but I played around with Ranking and wanted to show:

  • The Ranking within the Context (Year, Month) for the Top and Bottom 5.
  • The total Ranking regardless of Context for the Top and Bottom 5.

What I am having issues with is that I can’t get only the Top or Bottom 5 to show in the Total Ranking column. That column brings inn all of the customers.

Top5Bottom5_1

What I want is for the “Customer Rank Total” to only bring in the Top or Bottom 5, and show their total ranking like this:

Top5Bottom5_2

How do I do that? I have tried a few different ways, but I either get ERROR (The measure doesn’t work), I get ‘1’ back for all and maybe another error.

Anyone know how I can make it work like I want to?

I’ve attached my Power BI file.

DAX _25 - Top5Bottom5 06182023 Martin 2.pbix (689.8 KB)

1 Like
2

Hello @MartinO I will look at this tonight. In fact, I will start posting video solutions on my YouTube channel soon for these.

Talk soon

Paul

1 Like
3

Awesome. Will keep an eye out. :slight_smile: Subscribed to your YouTube channel!

4

Hello @MartinO,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve provided. Write the DAX measure as provided below alongwith the screenshot of the final results attched for the reference -

Customers Rank Total = 
IF(
    NOT ISBLANK( [Customer Ranking Rank] ) &&
    ISINSCOPE( Customers[Customer Names] ) ,
    CALCULATE(
        RANKX(
            ALL( Customers[Customer Names] ) , 
            [Total Sales] , ,
            DESC , 
            Dense ) ,
        ALL( 'Date' ) ,
        VALUES( Customers[Customer Names] ) ) )

Final Results
Final Results886×784 20.9 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Top 5 & Bottom 5 - Harsh.pbix (690.0 KB)

1 Like
5

Amazing. Thank you!

I am still struggling a bit with understanding the Context. I am where I starting to sort of understand it in my own head, but I would not be able to explain to to someone else easily written and verbally in a confident way.

So the IF checks:

  1. NOT ISBLANK([Customer Ranking Rank] ) checks that the Customer is ranked within the filters (Year, Month and Top 5, Bottom 5 or All)
  2. ISINSCOPE(Customers[Customer Names]) is the one I am struggling the most with understanding. From what I understand this function returns TRUE for all customers returned within the filters selected. Basically it says “Do these customers exists within the filters”?"

And then the CALCULATION function does the ranking calculation for those customers returned after filtering.

Is this correctly understood?

DAX code hidden so the post doesn't become too long 
Customers Rank Total = If(
    NOT ISBLANK([Customer Ranking Rank] ) && ISINSCOPE(Customers[Customer Names]),
    CALCULATE(
        RANKX(
            All(Customers[Customer Names]), 
            [Total Sales],,
            DESC, Dense),
        All('Date'),
        Values(Customers[Customer Names])
    )
)