Forum Discussion
summing differetn tables
Hello eveyone!
I'm just starting to use power BI and I hope you can help me with one problem.
The thing is I download data about our cpc campaigns from Google Analytics and we have two different traffic resources - Google and Yandex.
I download them to BI with two queries and use filters for each traffic source.
So I have two tables - one for Adwords and another one for Yandex.
In Yandex everything's ok, and such things like cost, cpc and cpl give me no trouble.
But in Google adwords I have to use mesures to get correct data as we have comissions and taxes.
For example, for Adwords Cost of cpc campaigns look like this: cost*1.18/0.9
Same for CPC and CPL.
My problem is that I don't know how to sum this data from Adwords and Yandex to get total cost, av. cpc and av. cpl.
7 Replies
Hello eveyone!
I'm just starting to use power BI and I hope you can help me with one problem.
The thing is I download data about our cpc campaigns from Google Analytics and we have two different traffic resources - Google and Yandex.
I download them to BI with two queries and use filters for each traffic source.
So I have two tables - one for Adwords and another one for Yandex.
In Yandex everything's ok, and such things like cost, cpc and cpl give me no trouble.
But in Google adwords I have to use mesures to get correct data as we have comissions and taxes.
For example, for Adwords Cost of cpc campaigns look like this: cost*1.18/0.9
Same for CPC and CPL.
My problem is that I don't know how to sum this data from Adwords and Yandex to get total cost, av. cpc and av. cpl.Your post is not quite clear. It could help if you displayed a brief sample of the 2 tables.
Sum could mean across the row, or it could be for the column of a table, or it could be a column of 1 table with a column of a different table.
Are there unique IDs common to the 2 tables that allow a join?...such that you seek a sum of 2 tables per ID?
AlexTandem If I understood you correctly you need to sum two columns that are from different tables. You can do that by creating a meaure in first table as Table1Measure = Sum([column]). Then create measure in table 2 as Table2Measure = Sum([column]). Then simply create another measure that is the sum of two previous measures.
It's almost what I need, thank you!
I did as you said, but now I see another problem.
I need to sum total spent money (that's ok now) but also sum per every day for both yandex and Google.
When i use your method, one of the traffic sources shows me exactly the same ammount of money spent per each day.
For example, if I use cost and date from Yandex table and cost from Google table, in Google all the spent money are the same for each day.
I attache screenshot to show you how if looks like
https://yadi.sk/i/vzJF8k71v7BWX
Is there any way to sum each day for different tables?
Thank you!
If you have two tables which are similar as below. You can try with following measure formula.
Totalad = SUMX ( Yandex, CALCULATE ( SUM ( adwords[adwords cost] ), FILTER ( adwords, adwords[Date] = Yandex[Date] ) ) )Best Regards,
Herbert
