topic DAX Formula for Average Unique Email Open Rate in DAX Commands and Tips

DAX Formula for Average Unique Email Open Rate

ChrisDg — Tue, 27 Aug 2024 14:18:58 GMT

Hi,

I am trying to figure out how to create a DAX formula for the average of another DAX formula. I already created a formula for showing the unique open rate:

Open Rate = SUM(Combined_EmailGroupOverview[Unique Opens])/SUM(Combined_EmailGroupOverview[Total Delivered])

This works correctly, but now I need to do an Average Open rate of all of the Open Rates from the DAX 'Open Rate' calculation, which I don't know how to do.

I tried these formula:

Average Open Rate =

AVERAGEX(

VALUES(Combined_EmailGroupOverview[Email Subject Line]),

[Open Rate]

)

And this formula:

Average Open Rate by Content Type =

AVERAGEX(

VALUES(Combined_EmailGroupOverview[Content Type]),

DIVIDE(

SUM(Combined_EmailGroupOverview[Unique Opens]),

SUM(Combined_EmailGroupOverview[Total Delivered]),

)

It got me these results when I filtered for the average open rate based on content type:

I know these averages aren't right and the 2nd formula just gave me the same result as the regular open rate formula. For example, if I see these open rates: 100.00%, 100.00%, 100.00%, 50.00%, 39.13% then I want the average to be 77.83%.

I want to do the average open rates of many different categories: content type, email send date, email subject line, etc. Do I have to do different formulas for each category that I want the averages from? This also spans across 5 years worth of data and over 1 millon rows.

Thank you,

Christopher Degler

Re: DAX Formula for Average Unique Email Open Rate

lbendlin — Tue, 27 Aug 2024 22:14:23 GMT

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: DAX Formula for Average Unique Email Open Rate

ChrisDg — Wed, 28 Aug 2024 13:56:11 GMT

Hi Ibendlin,

Here is the data:

Email Send Date	Content Type	Open Rate	Sum of Unique Opens	Sum of Total Delivered
3/10/2021 21:00	Tradeshow	100.00%	1	1
3/11/2021 22:00	Tradeshow	100.00%	1	1
3/11/2021 23:00	Tradeshow	100.00%	1	1
3/12/2021 13:00	Tradeshow	50.00%	1	2
3/12/2021 15:00	Tradeshow	80.00%	4	5
3/12/2021 16:00	Tradeshow	60.00%	3	5
3/15/2021 11:00	Tradeshow	21.51%	20	93

Thank you,

Chris

Re: DAX Formula for Average Unique Email Open Rate

ChrisDg — Wed, 28 Aug 2024 14:21:05 GMT

Hi Ibendlin,

Sorry to post a screenshot. I've tried adding my sample data set multiple different ways and it keeps giving me this error:

Sometimes it will let me post the sample data set and it says that the content has posted, but when I refresh the page the comment disappears.

Is there another way to do this or am I doing something wrong?

Thank you,

Chris

Re: DAX Formula for Average Unique Email Open Rate

lbendlin — Wed, 28 Aug 2024 16:01:30 GMT

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Re: DAX Formula for Average Unique Email Open Rate

ChrisDg — Wed, 28 Aug 2024 16:05:57 GMT

Hi Ibendlin,

I now see the sample data uploaded correctly based off of the link. It shown as Message 3 of 6. It must have had a lag in uploading.

Thank you,

Chris