cancel
Showing results 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

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
Community Support

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:

Hope this can help.

Best Regards,

Giotto Zhi

4 REPLIES 4
Community Support

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

Community Support

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:

Hope this can help.

Best Regards,

Giotto Zhi

Anonymous
Not applicable

I believe this worked. Thanks for your help!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors