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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.