Hi experts,
With the help of you I was able to calculate the number of new members using this measure:
=COUNTROWS(
FILTER(
ADDCOLUMNS(
CALCULATETABLE(
VALUES(Raw[Email]),
Raw[Event Type] = "Sign-up"
),
"Previous Purchases",
CALCULATE(
COUNTROWS(Raw),
FILTER(
ALL(tbDate),
tbDate[Date] < MIN(tbDate[Date])
)
)
),
[Previous Purchases] = 0
)
)
Basically I look at any "Sign-up" event, take unique member emails, and pick those with 0 purchases in previous months. The Pivot Table is set up by month.
Now I want to see how many purchases these new members made in each month. My solution is as following but I'm sure this is incorrect -- too few:
=SUMX(
ADDCOLUMNS(
FILTER(
ADDCOLUMNS(
CALCULATETABLE(
VALUES(Raw[Email]),
Raw[Event Type] = "Sign-up"
),
"Previous Purchases",
CALCULATE(
COUNTROWS(Raw),
FILTER(
ALL(tbDate),
tbDate[Date] < MIN(tbDate[Date])
)
)
),
[Previous Purchases] = 0
),
"New Sign-up Purchases",
CALCULATE(
COUNTX(Raw, Raw[Conversion Id]),
Raw[Event Type] = "Purchase",
)
),
[New Sign-up Purchases]
)
Since this is super long, let me explain:
Basically I took the table of new members and add another column that count the rows of "Purchase" event-type. The only new code is this part (aside from the SUMX at the top):
CALCULATE(
COUNTX(Raw, Raw[Conversion Id]),
Raw[Event Type] = "Purchase",
)
I thought this would give me the right number, but it didn't, and I'm not sure why. It might have something to do with the new member table as it only contains unique value of [Email]...
