none
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

Answers

  • 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 K. Beckett Tuesday, July 30, 2013 1:21 PM
    Tuesday, July 30, 2013 5:24 AM
    Answerer

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 K. Beckett Tuesday, July 30, 2013 1:21 PM
    Tuesday, July 30, 2013 5:24 AM
    Answerer