Forum Discussion

jonathanlyon's avatar
jonathanlyon
Regular Visitor
9 years ago
Solved

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 ...
  • jonathanlyon's avatar
    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