Need Help with Ranking Customers and Showing Totals based on Rank

DAX DAX Calculations
1

Hi Fellow Power BI Users -

I’m having a problem trying to link my Customer Rank Group table to my Average YTD Sales similar to what you did in your “Grouping & Segmenting Your Data With DAX Measure Logic - Advanced DAX” video.

See below for all measures. First off, I created a measure that calculates what the average YTD sales are for TY vs LY. Then I created a Customer Rank measure that ranks the Average YTD Sales. Then I created a separate table to store the Customer rankings like you explained in your “Secondary Table Logic Inside Of Power BI Using DAX” video. I can see the results as shown, but now I need to create a measure that gives me a totals for each Customer Group (i.e., Rank 1-25 = $$ All Average YTD Sales that fall into that rank, etc.). That’s the measure I’m struggling with. I tried following your “Grouping & Segmenting Your Data With DAX Measure Logic - Advanced DAX”, but I cannot make it work. The closest I can get gives me the same total Average YTD Sales for all 3 ranks. And I know why it’s doing that, it’s because there is no relationship between my Customer Rank Group table and my main fact table (where my customers are).

Here are the measures I’m using:

Average YTD Sales = calculate([Sales LYTD] + [Sales TYTD])/2

Sales LY = 
	CALCULATE( 
		[Total Sales] , 
		SAMEPERIODLASTYEAR( PBI_FSCAPF[PADDATE] ))

Sales TY = calculate(
		sum(PBI_AEP_Shipments_Multi[Ext Unit Price]),
			filter(PBI_AEP_Shipments_Multi,
    		PBI_AEP_Shipments_Multi[Year]=Year(Today())))

Customer Rank = rankx( all(PBI_AEP_Shipments_Multi[CUSTOMER NAME - CODE]), [Average YTD Sales], , DESC)

Customer Group = 
    calculate ( SELECTEDVALUE('Customer Rank Group'[Rank Group], Blank() ),
    filter(all('Customer Rank Group'),
    [Customer Rank] >= 'Customer Rank Group'[Min] &&
    [Customer Rank] < 'Customer Rank Group'[Max] ) )

Can you help steer me in the right direction?

Thanks,
Rose

customer%20rank%204
customer rank 4.JPG1301×622 93.3 KB

2

This will be the technique you need to use for that specific calc

I don’t think you’re far away. It certainly sounds like you understand what’s required.

This is the pattern below

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

You just need to replicate this pattern and input your numbers. You actually have everything you need already.

Let me know how you go.