Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]...
It's better if you could share your sample data which could reproduce your scenario and your desired output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
How to Get Your Question Answered Quickly
Regards,
Cherie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |