create a measure of totals by filtering one column, using the content of another

[ws690]

Hi,

I'm new to PowerBI so apologies if this is an obvious thing to do 🙂

I have a problem where I want to get the average cost per conversion. So the Total cost of all Campaign ads where a conversion has occured divided by the total number of conversions.

So I essentially want to find out where any conversion exists, which campaigns they realte to, then total all of the ad spend from those campaigns, divided by the total number of conversions. So any campaign where there have been no conversions is not inlcuded in the total.

Campaign Name	Ad version	Total Spent	Impressions	Clicks	Conversions
Campaign 1	1	7.71	313	5	1
Campaign 1	2	3.03	55	1	0
Campaign 1	3	2.18	39	24	0
Campaign 2	1	2.66	51	1	0
Campaign 2	2	1.45	30	23	0
Campaign 2	3	2.18	39	24	0
Campaign 3	1	1.8	20	3	1
Campaign 3	2	1.39	18	7	0
Campaign 3	3	1.05	15	34	0
Campaign 3	4	1.3	21	2	0
Campaign 4	1	2.22	33	5	0
Campaign 4	2	2.28	16	4	0
Campaign 5	1	1.79	18	8	0
Campaign 5	2	1.95	33	34	0
Campaign 5	3	1.18	12	12	0

Here's an example of the data table I'm working with.

Can anyone help?

Thanks in advance 

[johnt75]

Cost per conv =
var totalSpend = CALCULATE( SUM(Campaign[Total Spent]), Campaign[Conversions] > 0)
var totalConv = SUM(Campaign[Conversions])
return DIVIDE( totalSpend, totalConv)

[ws690]

Hi @johnt75 ,

Thanks for your response. This looks to be providing me with the cost for just the line items where the conversion occured.

I am actually looking to get - the total campaign spend, where there has been a conversion, rather than just that ad version.

So in the table below, the conversion on Campaign 1, should add upp all the totals for everything that is listed under Campaign 1, not just the top line.

Does that make sense?

Is that possible?

Thanks

[johnt75 · Accepted solution]

This will get the right result

Cost per conv =
var campaignsWithSpend = CALCULATETABLE( VALUES(Campaign[Campaign Name]), Campaign[Conversions] > 0)
var totalSpend = CALCULATE( SUM(Campaign[Total Spent]), campaignsWithSpend )
var totalConv = SUM(Campaign[Conversions])
return DIVIDE( totalSpend, totalConv)

[ws690]

Thank you so much 🙂