Create totals table from multiple tables

[jonathanlyon]

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 visits), Total Clicks, (DISP +SEM clicks) Total Impressions etc (the highlighted in yellow fields) and wondered if this is possible using DAX or should I do it in the source data in Excel?

The expected result would be a new table like this:

Country, Total Active Visits, Total Clicks, Total Impressions

I've tried SUMMARIZE but that only works on one table, I've tried a merge query but that doesn't help.

Any help would be welcome!

Thanks

Jonathan

[jonathanlyon · Accepted solution]

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

[v-caliao-msft]

@jonathanlyon,

In DAX expression, SUMMARIZE function accept only one table for the first parmater.  So in your scenario, you could create a summarize cournty table, and then add columns to this table by using REMATED or LOOKUPVALUE function.

RELATED function returns a related value from another table.
https://msdn.microsoft.com/en-us/library/ee634202.aspx
Lookupvalue function returns the value in result_columnName for the row that meets all criteria specified by search_columnName and search_value.
https://msdn.microsoft.com/en-us/library/gg492170.aspx

If this is not what you want, please provide us some sample data, so that we can try to give you a more detail solution.

Regards,

Charlie Liao

[GilbertQ]

Hi @jonathanlyon

What I would suggest doing is to Append the tables in the Query Editor

What this will do is to then Append them together. When the column names are the same it will append the data into the same columns.

And when the columns are different after appending they will then go into a new column with the existing name. (And where there ae no values, it will just be shown as null)

So when you now load the data you will then have all your data in one table.

You can then get the information you need from this one table, which will also include your Country names.