Forum Discussion

Roseventura's avatar
Roseventura
Responsive Resident
7 years ago

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

I'm trying to create a table 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

 

Need Help

2 Replies

    • Roseventura's avatar
      Roseventura
      Responsive Resident
      7 years ago

      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 ) )