Forum Discussion
jonathanlyon
9 years agoRegular Visitor
Create totals table from multiple tables
Hi I hope someone can help. I have two tables [display] and [sem] and a [country] table. I'd like to cerate a TOTAL TABLE that has the fields Country, Total Active Visits (DISP +SEM active ...
- 9 years ago
Huge thanks to Phillip Seamark who came up with an awesome solution whilst waiting to board a plane:-
Totals = Var MyVisits = SUMMARIZECOLUMNS( sem[Country SEM], "Total Active Visits SEM",SUM('sem'[Active Visits SEM]), "Total Clicks SEM" , SUM('sem'[Clicks SEM]), "Total Referrals SEM" , SUM('sem'[Referrals SEM]), "Total Spend SEM" , SUM('sem'[Spend]) ) var myDisplay = SUMMARIZECOLUMNS( 'display'[Country DISP], "Total Impressions",SUM('display'[Impressions]), "Total Active Visits DISP",SUM('display'[Active Visits DISP]), "Total Clicks DISP" , SUM('display'[Clicks DISP]), "Total Referrals DISP" , SUM('display'[Referrals DISP]), "Total Spend DISP" , SUM('display'[Spend]) ) var FirstTable = NATURALLEFTOUTERJOIN( SELECTCOLUMNS(countries,"Country",[country] & ""), SELECTCOLUMNS(myDisplay,"Country",[Country DISP] & "" , "Total Impressions" ,[Total Impressions] & "" , "Total Clicks DISP" ,[Total Clicks DISP]& "" , "Total Referrals DISP" ,[Total Referrals DISP]& "" , "Total Active Visits DISP" ,[Total Active Visits DISP]& "" , "Total Spend DISP" ,[Total Spend DISP] ) ) var FinalTable = NATURALLEFTOUTERJOIN( FirstTable, SELECTCOLUMNS( MyVisits, "Country",[Country SEM] & "", "Total Active Visits SEM",[Total Active Visits SEM], "Total Referrals SEM",[Total Referrals SEM], "Total Spend SEM",[Total Spend SEM], "Total Clicks SEM" , [Total Clicks SEM])) return FinalTable
jonathanlyon
9 years agoRegular Visitor
Huge thanks to Phillip Seamark who came up with an awesome solution whilst waiting to board a plane:-
Totals =
Var MyVisits = SUMMARIZECOLUMNS(
sem[Country SEM],
"Total Active Visits SEM",SUM('sem'[Active Visits SEM]),
"Total Clicks SEM" , SUM('sem'[Clicks SEM]),
"Total Referrals SEM" , SUM('sem'[Referrals SEM]),
"Total Spend SEM" , SUM('sem'[Spend])
)
var myDisplay = SUMMARIZECOLUMNS(
'display'[Country DISP],
"Total Impressions",SUM('display'[Impressions]),
"Total Active Visits DISP",SUM('display'[Active Visits DISP]),
"Total Clicks DISP" , SUM('display'[Clicks DISP]),
"Total Referrals DISP" , SUM('display'[Referrals DISP]),
"Total Spend DISP" , SUM('display'[Spend])
)
var FirstTable = NATURALLEFTOUTERJOIN(
SELECTCOLUMNS(countries,"Country",[country] & ""),
SELECTCOLUMNS(myDisplay,"Country",[Country DISP] & "" ,
"Total Impressions" ,[Total Impressions] & "" ,
"Total Clicks DISP" ,[Total Clicks DISP]& "" ,
"Total Referrals DISP" ,[Total Referrals DISP]& "" ,
"Total Active Visits DISP" ,[Total Active Visits DISP]& "" ,
"Total Spend DISP" ,[Total Spend DISP]
)
)
var FinalTable = NATURALLEFTOUTERJOIN(
FirstTable,
SELECTCOLUMNS(
MyVisits,
"Country",[Country SEM] & "",
"Total Active Visits SEM",[Total Active Visits SEM],
"Total Referrals SEM",[Total Referrals SEM],
"Total Spend SEM",[Total Spend SEM],
"Total Clicks SEM" , [Total Clicks SEM]))
return FinalTable