DAX Formula for Average Unique Email Open Rate

Question

Hi,&nbsp;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:&nbsp;&nbsp;Open Rate = SUM(Combined_EmailGroupOverview[Unique Opens])/SUM(Combined_EmailGroupOverview[Total Delivered])&nbsp;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.&nbsp;&nbsp;I tried these formula:Average Open Rate =AVERAGEX(&nbsp; &nbsp;VALUES(Combined_EmailGroupOverview[Email Subject Line]),&nbsp; &nbsp;[Open Rate])&nbsp;And this formula:&nbsp;Average Open Rate by Content Type =AVERAGEX(&nbsp; &nbsp;VALUES(Combined_EmailGroupOverview[Content Type]),&nbsp; &nbsp;DIVIDE(&nbsp; &nbsp; &nbsp; &nbsp;SUM(Combined_EmailGroupOverview[Unique Opens]),&nbsp; &nbsp; &nbsp; &nbsp;SUM(Combined_EmailGroupOverview[Total Delivered]),&nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp;))&nbsp;It got me these results when I filtered for the average open rate based on content type:&nbsp;&nbsp;&nbsp;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%,&nbsp;100.00%,&nbsp;100.00%, 50.00%, 39.13% then I want the average to be 77.83%.&nbsp;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,&nbsp;Christopher Degler&nbsp;&nbsp;

lbendlin · Answer

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

chrisdg · Answer

Hi Ibendlin,&nbsp;Here is the data:&nbsp;Email Send DateContent TypeOpen RateSum of Unique OpensSum of Total Delivered3/10/2021 21:00Tradeshow100.00%113/11/2021 22:00Tradeshow100.00%113/11/2021 23:00Tradeshow100.00%113/12/2021 13:00Tradeshow50.00%123/12/2021 15:00Tradeshow80.00%453/12/2021 16:00Tradeshow60.00%353/15/2021 11:00Tradeshow21.51%2093&nbsp;Thank you,&nbsp;Chris

chrisdg · Answer

Hi Ibendlin,&nbsp;Sorry to post a screenshot. I've tried adding my sample data set multiple different ways and it keeps giving me this error:&nbsp;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.&nbsp;&nbsp;Is there another way to do this or am I doing something wrong?&nbsp;Thank you,&nbsp;Chris&nbsp;&nbsp;

lbendlin · Answer

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

chrisdg · Answer

Hi Ibendlin,&nbsp;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.&nbsp;&nbsp;&nbsp;Thank you,&nbsp;Chris

Forum Discussion

DAX Formula for Average Unique Email Open Rate

5 Replies

Related content

Average formula

If formula

formula

DAX Formula Help

Average of Unique customers / Unique Countries:

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