3

I have a problem with calculating CPC in Tableau. I have the cost and the number of the click but Tableau is not calculating the right CPC. the formula I used : [Cost]/[Click] I attached two tables in this request. first shows the table which I calculated all KPIs in Zeppelin. the second the calculation in Tableau.

The whole data set has many null and 0 values, but it is the same data set used in zeppelin.

May I ask for help,how to solve this issue?
enter image description here
The result of CPC is not correct in Tableau.

sinsuren
  • 1,745
  • 2
  • 23
  • 26
HELENA AHMADI
  • 31
  • 2
  • The zeppelin image has not been uploaded here, but according to the T Tableau table, if I divide the cost/click : 76.25/67 it should become 1.13. How ever it is not true in Tableau. – HELENA AHMADI Aug 06 '16 at 09:03
  • My hypothesis would be, the CPC is calculating the average CPC for each row, and since lots of null data is available , these 0s influence on the total CPC – HELENA AHMADI Aug 06 '16 at 10:47
  • 3
    What happens if you do sum([cost])/sum([click])? – Bernardo Aug 06 '16 at 14:39
  • I second @Bernardo's suggestion. That's what I ended up doing with Google Analytics data and it matched the CPC value shown in the GA reporting site. – Sam M Aug 07 '16 at 04:06

2 Answers2

5

Helena,

the issue is that Tableau is using aggregate functions when you add measures, and in this case of CPC calculation, it's not correct.

Tableau is doing [cost]/[click] division (on row-level) and then simply averages all those numbers (you are basically calculating an average of an already average number).

What you are after is a bit different and you have to fix the math to make sure all costs are aggregated first and then divided by sum of all clicks, so:

SUM([cost]) / SUM([clicks])

This will give you the correct and mathematically sound numbers.

Hope this helps.

Petr Havlik
  • 3,307
  • 1
  • 19
  • 17
0

SUM([cost]) / SUM([clicks]) will resolve the issue

girish
  • 56
  • 2