Dynamic Grouping with Ranking is duplicating Customers

[Rose]

Hi there,
I created a dynamic ranking table for my Top 10 Customers following the “Dynamic Grouping with Ranking Logic” video. This works and has worked very well form me. However, this time I’m trying to put my ranked customers in a matrix table where the top category is the Rank Group and the category below that is the Customer Group. (Don’t get confused - my “Customer Group” is really the Customer Name. The Customer Rank Group is my table shown below.)

Here is my measure:

Total Sales by Rank = 
CALCULATE( [Total Sales],
	FILTER( VALUES( PBI_AEP_CustMaster135[Customer Group] ),
		COUNTROWS( 
			FILTER( 'Customer Rank Group',
				RANKX( ALL( PBI_AEP_CustMaster135[Customer Group] ), [Total Sales],, DESC ) >= 'Customer Rank Group'[Min] 
				&& RANKX( ALL( PBI_AEP_CustMaster135[Customer Group]), [Total Sales],, DESC ) <= 'Customer Rank Group'[Max] ) )
			> 0 ) )

My Customer Rank Group table is this:

Here is my result. As you can see the highlighted Customer Names show up in both the Top 10 (which they are not) without totals and also under “All Others” with Totals (as it should be). What is causing this?

[sam.mckay]

Context is the key here.

Are you totally understand what the top 10 is at each different context (or result) in the table?

That is the key to understanding the behaviour here.

When you place the quarterly context in the matrix you are now working out the top 10 in each different quarter, not the overall top 10 from the beginning of time.

The way to work these out is to break the formula down. When looking just at the ranking you’ll see it shows a greater spread of ranks for customers

What needs to be done here is to remove filters from the date table, which I did with the below

So to me the matrix looks correct. It is just that some of your customers appear in the top 10 only occasionally in each quarter and then are in your other group some other quarters.

This is where understanding the context well is crucial and then also how your formula reacts to it.

To get the overall top 10 no matter the date context you need something like this.

Then I took this back into the grouping formula

Customer Sales by Group (All) =
VAR OverallRanking = [Customer Ranking]

RETURN
CALCULATE( [Total Sales],
    FILTER( VALUES( Customer[Customer Names] ),
       COUNTROWS(
           FILTER( 'Customer Groups',
               OverallRanking > 'Customer Groups'[Min]
               && OverallRanking <= 'Customer Groups'[Max] ) )
      > 0 ))

[Rose]

Sam,
I implemented your suggestions and got it to work. You are right (as always!) I was not considering ALL of the dates. Once I changed the context, it worked as expected.

Thank you!

Rose