Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Ranking

Hi all,

 

I'm trying to rank each customer's purchases chronologically based on their purchase date starting with "1" for each customer's first purchase and then sequencing numercially with each additional purchase (i.e. their 2nd purchase would populate rank as "2", etc.) 

I want the rank to start over from "1" with each individual client. As you can see for below for client, C100456 the formula is working correctly, but for client C100217 it isn't. 

 

 

This is my current formula: 

Rank = RANKX(ALLEXCEPT(ep_Sales_Invoice_Line, ep_Sales_Invoice_Line[Customer_Name]), CALCULATE(MAX(ep_Sales_Invoice_Line[INVOICENUMBER])),,ASC, DENSE)-1

 

Thank you for all your help! 🙂 

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Rank invoicenumber base measure: = 
RANKX (
    FILTER (
        ALL ( ep_Sales_Invoice_Line ),
        ep_Sales_Invoice_Line[Customer_Account_Number]
            = MAX ( ep_Sales_Invoice_Line[Customer_Account_Number] )
    ),
    CALCULATE ( MAX ( ep_Sales_Invoice_Line[INVOICENUMBER] ) ),
    ,
    ASC
)

     

    Rank invoicedate base measure: = 
RANKX (
    FILTER (
        ALL ( ep_Sales_Invoice_Line ),
        ep_Sales_Invoice_Line[Customer_Account_Number]
            = MAX ( ep_Sales_Invoice_Line[Customer_Account_Number] )
    ),
    CALCULATE ( MAX ( ep_Sales_Invoice_Line[Invoice_Date] ) ),
    ,
    ASC
)

     

    Rank measure: =
RANKX (
    FILTER (
        ALL ( ep_Sales_Invoice_Line ),
        ep_Sales_Invoice_Line[Customer_Account_Number]
            = MAX ( ep_Sales_Invoice_Line[Customer_Account_Number] )
    ),
    [Rank invoicedate base measure:] + [Rank invoicenumber base measure:] / 100,
    ,
    ASC
)

3 Replies

  • rodrigosan's avatar
    rodrigosan
    Responsive Resident
    4 years ago

    Hello, Anonymous how are you?
    I don't know if my solution can help you.
    What I did was create a key in the fSales table with the date in numeric format (yyyymmdd) and with the CustomerKey.
    After I duplicated this table, I left only the Data / CustomerKey / Key columns.
    I created a group and inserted an index inside the group.
    Then I expanded and merged with fSales table based on Key.
    Then I disabled the copy load.
    I have my example file here at this link: https://easyupload.io/mjew8f

     

    Did I solve your problem?
    Please mark as solution so others can find this solution.
    https://www.linkedin.com/in/rodrigosanpbi/

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Rank invoicenumber base measure: = 
RANKX (
    FILTER (
        ALL ( ep_Sales_Invoice_Line ),
        ep_Sales_Invoice_Line[Customer_Account_Number]
            = MAX ( ep_Sales_Invoice_Line[Customer_Account_Number] )
    ),
    CALCULATE ( MAX ( ep_Sales_Invoice_Line[INVOICENUMBER] ) ),
    ,
    ASC
)

     

    Rank invoicedate base measure: = 
RANKX (
    FILTER (
        ALL ( ep_Sales_Invoice_Line ),
        ep_Sales_Invoice_Line[Customer_Account_Number]
            = MAX ( ep_Sales_Invoice_Line[Customer_Account_Number] )
    ),
    CALCULATE ( MAX ( ep_Sales_Invoice_Line[Invoice_Date] ) ),
    ,
    ASC
)

     

    Rank measure: =
RANKX (
    FILTER (
        ALL ( ep_Sales_Invoice_Line ),
        ep_Sales_Invoice_Line[Customer_Account_Number]
            = MAX ( ep_Sales_Invoice_Line[Customer_Account_Number] )
    ),
    [Rank invoicedate base measure:] + [Rank invoicenumber base measure:] / 100,
    ,
    ASC
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you so much, this fixed our problem 🙂