cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

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

Frequent Visitor

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

Frequent Visitor

I believe this worked. Thanks for your help!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors