# Problem with RANKX when calculating Gain/Loss between 2 Ranked columns

I’m trying to create a visual that shows customers, LY Sales, TY Sales and Gain/Loss. My customers are ranked by Sales (top 25, next 25, other) over a 2 year period (TY and LY). When I try to use my filter to see only the customers in the 1-25 rank, the table on the left (below) WITHOUT the Gain/Loss measure displays only those customers (which is good). But as soon as I add the Gain/Loss column, the table will display ALL the customers, regardless of rank.

How can I get the table on the right to display only those customer which I have filtered out?

Sorry, I had to blank out the customers.

Thanks,

Rose

Adding the formulas you’re using would be helpful as it’s difficult to know without seeing anything.

Have you tried just wrapping you formula in simple IF( Some Measure = 0, BLANK(), Gain/Loss Measure)

This should do it quite easily.

Here are my measures:

Avg Total Sales = calculate( [Sales 2Y] + [Sales LY] + [Sales TY RR])/3

Sales 2Y =
calculate(
sum(PBI_AEP_Shipments_Multi[Ext Unit Price]),
filter(PBI_AEP_Shipments_Multi,
PBI_AEP_Shipments_Multi[Year]=[Year 2Y]))

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

Sales TY RR =
CALCULATE([Sales TY]/[Current Month])*12

Sales LY by Rank =
CALCULATE( [Sales LY],
FILTER( VALUES( PBI_AEP_CustMaster135[CUSTOMER NAME - CODE]),
COUNTROWS(
FILTER( ‘Customer Rank Group’,
RANKX( ALL( PBI_AEP_CustMaster135[CUSTOMER NAME - CODE] ), [Avg Total Sales], DESC ) >= ‘Customer Rank Group’[Min]
&& RANKX( ALL( PBI_AEP_CustMaster135[CUSTOMER NAME - CODE] ), [Avg Total Sales], DESC ) <= ‘Customer Rank Group’[Max] ) )

0 ) )

Sales TY RR by Rank =
CALCULATE( [Sales TY RR],
FILTER( VALUES( PBI_AEP_CustMaster135[CUSTOMER NAME - CODE]),
COUNTROWS(
FILTER( ‘Customer Rank Group’,
RANKX( ALL( PBI_AEP_CustMaster135[CUSTOMER NAME - CODE] ), [Avg Total Sales], DESC ) >= ‘Customer Rank Group’[Min]
&& RANKX( ALL( PBI_AEP_CustMaster135[CUSTOMER NAME - CODE] ), [Avg Total Sales], DESC ) <= ‘Customer Rank Group’[Max] ) )

0 ) )

Gain/Loss TY v LY by Rank =
CALCULATE( [Gain/Loss TY v LY],
FILTER( VALUES( PBI_AEP_CustMaster135[CUSTOMER NAME - CODE] ),
COUNTROWS(
FILTER( ‘Customer Rank Group’,
RANKX( ALL( PBI_AEP_CustMaster135 ), [Avg Total Sales], DESC ) >= ‘Customer Rank Group’[Min]
&& RANKX( ALL( PBI_AEP_CustMaster135 ), [Avg Total Sales], , DESC ) <= ‘Customer Rank Group’[Max] ) )

0 ) )