Rank Products, Stores (Over all time, Latest Month, Previous Month)

[MarkCBB]

Hi there,

I have the following tables:

Sales - This is may Fact Table

Calendar - (With dates)

Product - This is a unique list

Stores - This is a unique list

I am looking to crete the following DAX Measures.

Top 10 Ranked Products over all Time, then for the latest Month, and for the Previous Month (Same for stores - But I can do this once I know how to do it for the products.

I then want to know the top 10 rank movments from Previous Month to latest Month, i.e. Product X went from Rank 56 to Rank 12 etc....

and then the same for Products that moved negitivly, i.e. Product Y went from Rank 2 to Rank 44. (so the worst Movments)

Our Products are fluctuating alot from month to month so we are trying to track the issue down, this will assist us in understanding the problem.

Regards,

Mark B

[BetterCallFrank · Accepted solution]

Hi @MarkCBB

to get you started, here are some DAX formulas for

1) RANK of all times

Rank all time = 
IF( HASONEVALUE( 'Product'[Product] ),
  RANKX( ALL( 'Product' ), SUMX( RELATEDTABLE( _Sales ), _Sales[Sales Amt] ) )
)

2) RANK current month

Rank Curr Month = 
IF( HASONEVALUE( 'Product'[Product] ),
  RANKX( ALL( 'Product' ),
    CALCULATE( 
      SUMX( _Sales, _Sales[Sales Amt] ), 
      YEAR( _Sales[Date] ) = YEAR( NOW() ), 
      MONTH( _Sales[Date] ) = MONTH( NOW() ) 
    )
  )
)

3) RANK previous month

Rank Prev Month = 
VAR YYYYMM = 
  IF(
    MONTH(NOW()) > 1,
    YEAR(NOW())*100+MONTH(NOW())-1,
    (YEAR(NOW())-1)*100+12
  )
RETURN
IF( HASONEVALUE( 'Product'[Product] ),
  RANKX( ALL( 'Product' ),
    CALCULATE( 
      SUMX( _Sales, _Sales[Sales Amt] ), 
      YEAR( _Sales[Date] ) * 100 + MONTH( _Sales[Date] ) = YYYYMM 
    )
  )
)

I put the demo workbook at https://dl.dropboxusercontent.com/u/2676210/Rank%20All%20time%2C%20curr%20month%2C%20prev%20month.pb... for you to download.

As for the movements in the ranks: you can now of course create a table visual, put in the products, current month rank and previous month rank - and/or make new measures to calculate the movements

HTH,

Frank