Weighted Average for Email Open Rate

[YaleSOM]

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%

[KGrice]

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]

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:

[KGrice · 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.