Forum Discussion
Solved
Distinct Count By Group - Average
Hello, I am trying to find the unique open rate (count each email address once for each message it opens) for a set of messages. I can successfully see the unique open rate for each message stand a...
It's a bit hard to say without seeing some sample data, but maybe you could create an [Open Count] measure something like the following:
Open Count = SUMX(VALUES(Email_Activity[Message Title]),
CALCULATE (DISTINCTCOUNT (Email_Activity[Recipient E-mail] ) , Email_Activity[Opened] = 1)
)Then your Open Rate measure would reference this measure
Open Rate = [Open Count] / SUM( Email_Activity[Sent] )
d_gosbell sorry for the late response, but yes I have multiple rows for each time a email address opens an email. Unfortuntely that is something I cannot change as part of the export. I also have 1 row for each time someone clicks on an email. Do you have any suggestions?
It's a bit hard to say without seeing some sample data, but maybe you could create an [Open Count] measure something like the following:
Open Count = SUMX(VALUES(Email_Activity[Message Title]),
CALCULATE (DISTINCTCOUNT (Email_Activity[Recipient E-mail] ) , Email_Activity[Opened] = 1)
)
Then your Open Rate measure would reference this measure
Open Rate = [Open Count] / SUM( Email_Activity[Sent] )