Adding Customers First Purchase Type to All Rows

[Kipple123]

I'm trying to add a calculated column to my table so that I can slice the data by the marketplace that customers were aquired on so I can do lifetime value analysis.  I've got a ranking function that's working and labels each customer's order as first order, second order, etc... Now I want to add a column that takes the marketplace [type] of the 1st order and puts it on all rows.  I've been trying to use an If function but it's not working since it only looks each row's context.  Each customer has it's own unique customerID which is what I would like to use to filter/assign the first purchase type to.

For the table below I would like to change Repeat Purchase to Kickstarter in this instance, since the first purchase this customer made was on Kickstarter.  My calculated colum is using this:

1stPurchaseType = IF([Rank]=1,Sales[type],"Repeat Purchase")

I haven't been able to figure out how to get the else condition to return the sales[type] of the row with [rank]=1

[v-yuta-msft]

@Kipple123 ,

---

@Kipple123 wrote:

I'm trying to add a calculated column to my table so that I can slice the data by the marketplace that customers were aquired on so I can do lifetime value analysis.  I've got a ranking function that's working and labels each customer's order as first order, second order, etc... Now I want to add a column that takes the marketplace [type] of the 1st order and puts it on all rows.  I've been trying to use an If function but it's not working since it only looks each row's context.  Each customer has it's own unique customerID which is what I would like to use to filter/assign the first purchase type to.

---

I'm afraid I could not find the customerID in your sample data. And could you clarify more details about the first customer purchase and show the expected result if possible?

Regards,

Jimmy Tao

[Kipple123]

@v-yuta-msft Thanks for helping out.  Here's some simplified sample data:

I'm trying to add the column in yellow, where each customer ID is labeled by the market place where they made their first purchase.  I'm trying to do this so that I can calculate lifetime value of customers acquired on different platforms.  I also am interested in doing analysis such as, if the first purchase type is Kickstarter, what % of those customers make a later purchase on Amazon.  The rank column labels each line as being the customers 1st purchase, 2nd purchase, etc....

[Kipple123 · Accepted solution]

I've got a work around functioning but I think there should be a more elegant solution to the problem.

Here's the work around:

In Power Query convert the Type to a number, (e.g. kickstarter = 1, amazon = 2, etc...) by adding a conditional column(NumberType).

Then in PowerBi assign an initial purchase type using this DAX:

Initial Purchase = VALUE(CALCULATE(MAX(Sales[NumberType]),ALLEXCEPT(Sales,Sales[customerId]),Sales[Rank]==1))

Then convert that back to a string using a switch function:

FirstPurchaseType = SWITCH(Sales[Initial Purchase], 1, "KICKSTARTER", 2, "AMAZON", 3, "SHOPIFY", 4, "WALMART", "OTHER")