topic Re: create a measure of totals by filtering one column, using the content of another in DAX Commands and Tips

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

ws690 — Fri, 25 Mar 2022 15:07:50 GMT

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

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

johnt75 — Mon, 28 Mar 2022 09:15:57 GMT

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

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

ws690 — Mon, 28 Mar 2022 10:10:57 GMT

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

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

johnt75 — Mon, 28 Mar 2022 10:20:44 GMT

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)

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

ws690 — Mon, 28 Mar 2022 12:06:19 GMT

Thank you so much 🙂