Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Assign ranks with multiple invoices from multiple customer IDs

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. 

 

1 ACCEPTED SOLUTION

Hi,

 

I create a sample to test:

1.PNG

Then try this rank column:

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

The result shows:

2.PNG

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

3.PNG
Here is my test pbix file:

pbix 

Hope this can help.

 

Best Regards,

Giotto Zhi

View solution in original post

4 REPLIES 4
v-gizhi-msft
Community Support
Community Support

Hi,

 

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

32.PNG

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]:

33.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

 

Anonymous
Not applicable

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 

 

Capture.PNG

 

 

Hi,

 

I create a sample to test:

1.PNG

Then try this rank column:

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

The result shows:

2.PNG

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

3.PNG
Here is my test pbix file:

pbix 

Hope this can help.

 

Best Regards,

Giotto Zhi

Anonymous
Not applicable

I believe this worked. Thanks for your help! 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors