Assign ranks with multiple invoices from multiple customer IDs

[Anonymous]

I am trying to calculate total sales based on invoice amount. For example: A customer may have purchased four different times, therefore assigning four different invoice numbers. I am using "invoice date" in ascending order as either transaction 1, 2, 3 etc (if they purhcased on 1/1 for $20 and 1/5 for $40, first invoice is $20 and second invoice is $40). I've been using MIN and MAX based on "invoice date" to calculate total sales based on customer ID. This works fine until they purhcase more than TWICE. 

If Customer ID 123 purchased on 1/1, 1/5, 1/8 and 1/10 for $20, $30, $40 and $50, I would like to create a dax measure that represents all of these sales figures based on what invoice (in ascending order) they might be on? So my measure would look like this: 

First Invoice for $20, Second for $30, Third for $40 and Fourth for $50. 

I've tried ranking these invoices based on "Invoice Date" but that just created more problems. Any help would be greatly appeciated. 

[v-gizhi-msft]

Hi,

According to your description, i create a sample to test:

Then try this measure:

Invoice No. = RANKX(CALCULATETABLE(DISTINCT('Table'[Invoice Date]),FILTER(ALLSELECTED('Table'),'Table'[Customer ID] in FILTERS('Table'[Customer ID]))),CALCULATE(SUM('Table'[Payment])),,ASC,Dense)

The result shows the rank grouped by [CustomerID]:

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

Thank you for the reply. I don't think I explained myself well enough, I apologize. What I want is to run averages, max and mins on any second invoice, third invoice, fourth etc. See attached. What you see is Cust ID filtered down to just that one customer (SW10671)... That one customer has purchased six different times. The one unique thing about this data set is Invoice Number (Invoice Num) is unique and it assigns a new invoice number chronologically so I know we can use Invoice Num as the RANK. What i want the "Rankings" column to show is 1 for Inv Num: 264112, 2 for Inv Num: 264113, 3 for Inv Num: 264629.... and 5 for Inv Num: 267206... 

I also am hoping that when I unfilter Cust ID from SW10671 to ALL, that those exact same numbers will still show in "Rankings". What i intend to do is calculate second invoice, third invoice, fourth etc totals... Thank you 

[v-gizhi-msft · Accepted solution]

Hi,

I create a sample to test:

Then try this rank column:

Rank = RANKX('Table',VALUE('Table'[Invoice Num]),,ASC,Dense)

The result shows:

When applying filter to table visual, it still shows the rank starting from 1:

Here is my test pbix file:

pbix 

Hope this can help.

Best Regards,

Giotto Zhi

[Anonymous]

I believe this worked. Thanks for your help!