Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculated Measure or Column based on 2 rows for Conversion Rate

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!

1 REPLY 1
HotChilli
Super User
Super User

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

Annotation 2019-08-02 001740.png

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.