# Return last seller per item using DAX calculation

### Question

• I'm wondering if it's possible to retrieve the last seller for items sold. I've created a sample workbook (Excel 2013) with some dummy data available here: http://sdrv.ms/15R2WAf. I want to create a calculated column that returns the last seller for an item. When a particular item is purchased by multiple sellers on the same day, I want to return the seller with the highest sales amount. I have this DAX calculation (would be added as a calculated column on the Item table), but it only returns the seller with the highest seller ID because I haven't figured out a way to include a filter to include the sales amount in the calculation.

=LOOKUPVALUE(Seller[Seller Name], Seller[Seller ID], CALCULATE(MAX(Sales[Seller ID]), LASTDATE(Sales[Date])))

In the sample workbook that I've provided, item Shoes should have a last seller of Purple and not Brown.

Thoughts, opinions or suggestions?

Thanks!

Friday, July 26, 2013 6:10 PM

• to also consider the ordering by [Amount] you have to use this one:

```=LOOKUPVALUE(
Seller[Seller Name],
Seller[Seller ID],
CALCULATE(
MAXX(
TOPN(
1,
Sales,
Sales[Amount], 0,
Sales[Seller ID], 0),
Sales[Seller ID]),
LASTDATE(Sales[Date])))```
hth,
gerhard

- www.pmOne.com -

• Marked as answer by Tuesday, July 30, 2013 1:21 PM
Tuesday, July 30, 2013 5:24 AM

### All replies

• Hi K.Beckett

You can use below formula :

```LOOKUPVALUE(
Seller[Seller Name],
Seller[Seller ID],
LOOKUPVALUE(
Sales[Seller ID],
Sales[ID],
CALCULATE(MAX(Sales[ID]), LASTDATE(Sales[Date]))
)
)```
Regards,
Tuesday, July 30, 2013 3:16 AM
• to also consider the ordering by [Amount] you have to use this one:

```=LOOKUPVALUE(
Seller[Seller Name],
Seller[Seller ID],
CALCULATE(
MAXX(
TOPN(
1,
Sales,
Sales[Amount], 0,
Sales[Seller ID], 0),
Sales[Seller ID]),
LASTDATE(Sales[Date])))```
hth,
gerhard

- www.pmOne.com -

• Marked as answer by Tuesday, July 30, 2013 1:21 PM
Tuesday, July 30, 2013 5:24 AM