Calculated Measure or Column based on 2 rows for Conversion Rate

[Anonymous]

Hi All

Relatively new to PowerBi so please be patient. And sorry if this belongs in a different thread.

I work in email marketing and trying to calculate Conversion Rate which is 'Transactions' divided by 'Clicks'. 

I've been able to do this, however we split our regions out and the UK has 2 segments (rows in my table). This means that I would take the total transactions listed for the UK (which is marked against 1 row and no the other), and divide it by the sum of the clicks from both UK records on the same day. 

Any ideas on how I can do this? I've included an example table below of what this looks like currently.

Email Campaign  |   Clicks   |   Transactions   |   Conversion Rate

UK 1                    |  100        |          10            |             0.1%

UK 2                    |   50         |                          |

EU                       |     40       |           2             |              0.05%

So what I really want to achieve is

Email Campaign  |   Clicks   |   Transactions   |   Conversion Rate

UK 1                    |  100        |          10            |             0.67% <--(10/(100+50)

UK 2                    |   50         |                          |

EU                       |     40       |           2             |              0.05%

Thanks in advance!

[HotChilli]

In Power Query, if you add a column which is the first 2 letters of the email campaign

Table.AddColumn(#"Changed Type", "Brexit Column", each Text.Start([#"Email Campaign"], 2), type text)

Sorry about the name (I couldn't resist it)

Then create a measure in powerbi (format it as a percentage)

Conversion Rate Chilli = DIVIDE(SUM(MailCampaign[Transactions]),SUM(MailCampaign[Clicks]))

Drag the column on to a visualisation and drag the measure on and you'll get something like this

The good news is that conversion rates have gone up (x 100) from your example.