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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

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
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

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

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.