cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YaleSOM
Frequent Visitor

Weighted Average for Email Open Rate

My data table, called "Email Stats" contains analytics for each email campaign. I'm trying to create a new measure that calculates the weighted average of email open rate across all 50 email campaigns (rows).  

 

I'm a new Power BI user and having trouble getting this calculation to work. Can someone help me with the correct syntax for the weighted average of the email open rate.

 

 COL A              COL B

Opens:             Open Rate:

32                     62%

1055                 26%

334                   36%

67                     14%

 

 

Excel formula to calculate weighted average of Open Rate:

=SUMPRODUCT(B2:B5,A2:A5)/SUM(A2:A5)

28%

 

 

 

1 ACCEPTED SOLUTION

That error means OpenRateSum doesn't work as a measure because it's trying to do a Sum on a string, i.e., text. I suspect that the Open Rate is a string in your table, rather than an actual number. 25% is probably the number 25 and then the percent sign, not 0.25. 

 

You should be able to tell in your Edit Queries. The left-hand corner of each column has a symbol that indicates the data type. Open Rate should be 1.2, not ABC. If you see ABC, you might be able to click it and select 1.2 instead and see if it auto-converts correctly, which will depend on how it's stored. If that doesn't work and you're still stuck, post back.

 

Once that error's cleared, you'll be able to use Weighted Average in the card visual.

View solution in original post

3 REPLIES 3
KGrice
Solution Sage
Solution Sage

Hi @YaleSOM. If that's all of your columns, you'll need to create three new measures. How you name them is up to you.

 

OpensSum = SUM(TableName[Opens])

OpenRateSum = SUM(TableName[Open Rate])

Weighted Average = SUMX(VALUES(TableName[Opens]), ([OpensSum]*[OpenRateSum])) / [OpensSum]

 

The first two are pretty basic sums. The third uses SUMX, which can be used as a SUMPRODUCT equivalent, though I've never thought of it that way until this question. It's purpose is to calculate an expression at every row of a table. In this case, that table is the unique values in the Opens column. Right now, that's every row of your table. If you had a unique way to identify your rows, like a Category, I'd use that instead, as I imagine Opens isn't necessarily unique.

 

Either way, SUMX will multiply your Opens and Open Rate at each row of the table, and then the last part divides the result so far by the total of your Opens.

YaleSOM
Frequent Visitor

Hi @KGrice,

 

Thank you for your help. I followed your directions, however, I received an error message when I tried to use the "Weighted Average" as a card in my dashboard. Do you have ideas what is happening?  Below is the message I received:

 

Error Message:

MdxScript(Model) (1, 47) Calculation error in measure 'Email Stats'[OpenRateSum]: The function SUM takes an argument that evaluates to numbers or dates and cannot work with values of type String.

Stack Trace:

 

 

 

That error means OpenRateSum doesn't work as a measure because it's trying to do a Sum on a string, i.e., text. I suspect that the Open Rate is a string in your table, rather than an actual number. 25% is probably the number 25 and then the percent sign, not 0.25. 

 

You should be able to tell in your Edit Queries. The left-hand corner of each column has a symbol that indicates the data type. Open Rate should be 1.2, not ABC. If you see ABC, you might be able to click it and select 1.2 instead and see if it auto-converts correctly, which will depend on how it's stored. If that doesn't work and you're still stuck, post back.

 

Once that error's cleared, you'll be able to use Weighted Average in the card visual.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.