Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Senthil_Kumar
Frequent Visitor

ALL/ALLSelected in summarize table not working

I have a scenario,

we send multiple emails to users and in return we get leads generated due to the emails we send. Now, there are chances that a leads submitted by a user might have received more than 1 email and we want to give credit to all the email sent instead of earliest or latest email.

Now my issue is based on the email sent date, the number of email a per received will change and the credit given to emails will also change.

 

For ex: For a date range, I have 1 lead and 3 emails were sent to that person, than each will get the lead credit as 1/3 each. If I change the date range there might be chances that instead of 3 emails I might have only 2 and 3rd is out of the daterange, in such case the email lead credit should be 1/2 and not 1/3.

Sample Data:

Lead_idEmail_sent_idEmail_TypeEmail Sent date
L1E1Campaign1/1/2022
L1E2Lead Autoresponder1/2/2022
L2E3Lead Nurture1/3/2022
L3E1Campaign1/1/2022
L3E2Lead Autoresponder1/2/2022
L3E3Lead Nurture1/3/2022

 

Required output:

Email TypeLead Credit
Campaign 
Lead Autoresponder 
Lead Nurture 

 

I am trying to use summarize/rollup or a table function to filter the whole data on the fly basis date range selected, and than calculating the for each lead how many email were sent, but when I put that measure in the above table, the number of email sent is getting filtered basis email type which I don't wont. for 1 lead I have 2 emails . one in campaign and another in lead nurture email type, the denominator should be 2 irrespective or email type, but now its getting filtered to 1 each and lead credit is 1 & 1 instead of 0.5 each.

 
Lead Credit=

VAR
GRPBY =
GROUPBY(view_emailperformance,
view_emailperformance[Lead_id],
"cnt",COUNTX(CURRENTGROUP(),view_emailperformance[Email_sent_id]))

VAR
GRPBY2 = ADDCOLUMNS(GRPBY,"cnt2",1/[cnt])
Return SUMX(GRPBY2,[cnt2]*[cnt])
1 REPLY 1
amitchandak
Super User
Super User

@Senthil_Kumar , Try measure like

 

divide(istinctcount(Table[Lead_id]), distinctCOUNT(Table[Email_sent_id]))

 

or

 

Sumx(Values(Table[Lead_id]), calculate( divide(istinctcount(Table[Lead_id]), distinctCOUNT(Table[Email_sent_id])) ))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors