Calculating CTR and display in the right format in Power PI desktop

[ChingChiu]

Hello, I need help!

I have a lot of impression and click data, and I wanted to show CTR in the Power BI dashboard. 

I have a roll of data call "PowerBI click" and one for "PowerBi Impression"

I did a new column measures of CTR=divide([PowerBi clicks],[PowerBi impression]). 

I checked the formulas, it seems correct, but when I put it on the visualization, the CTR returned as 1.07K, instead of 1.07%. (the total impressions is 319M, and total clicks is 673K, so the CTR should returned as 0.21%, Not 1.07K).

I tried to go back to change the new measure (CTR) type to percentages, but i dont see the new measure in edit query. 

Can someone please help me?

Thank you!

C.C.

[ChingChiu · Accepted solution]

hello all, 

thank you for trying to help me solved the issues i have with CTR formula and display format in Power BI.

I somehow stumbled a solution and was able to solve it. 

What I did was i create 2 new measure columns, 

1) TOTAL Impressions  - which sum of all the impressions available in the data sheet.

2) TOTAL Clicks - sum all the clicks avaiable in the data sheet. 

Once that's done, i created another measure columns call eCTR

eCTR = (TOTAL Clicks / TOTAL Impression)*100 

The result was correct, and when display in the report using visualization, there's no additional formating need to be done. 

Thanks and i hope this also helps others with similar issues.

CC

[v-jiascu-msft]

@ChingChiu,

Hi C.C.

The measures aren't in the Query Editor. That's why you didn't see it there. 

1. Which visualization did you use? The Card visual could be great in your scenario.

2. It seems [PowerBi clicks] and [PowerBi impression] are measures. Could you please share the formulas of them?

Best Regards!

Dale

[ChingChiu]

Hello all, thank you for the answers!

I tried a few times and no successful. I am attaching the screen shots of the original data and visual card.

In the original data sheet, there is impressions, clicks and ctr  - these are extract straight from the data source, no formulas added. 

In the last right hand column, you will see CTR1, this is the new column with formulas i entered (CTR1 = divide('Power BI Data' [Clicks], 'Power BI data' [Impressions]). Normally when we calculate CTR, the formulas is CTR = (clicks/impressions), but the same formulas doesn't seem to work in Power BI. 

The only thing i need to do is to make sure the power bi can calculate CTR correctly, and the visual card can reflect the true value accordingly.

I have been searching for video, blogs and commuity post, but still no luck. 

Please help!!!!

Thank you!

Ching

[ChingChiu]

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[Ashish_Mathur]

Hi,

In the Fields pane on the right hand side, type CTR to search for the measure in all the tables you have.  Click on that measure and edit the formula in the formula bar.  You can also choose the formatting in the Ribbon.