cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jonathanlyon Frequent Visitor
Frequent 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 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

 

pbi - sem.PNGpbi - display.PNGpbi - countries.PNGpbi - fields.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
jonathanlyon Frequent Visitor
Frequent Visitor

Re: Create totals table from multiple tables

 

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

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Create totals table from multiple tables

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.


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog
Moderator v-caliao-msft
Moderator

Re: Create totals table from multiple tables

@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

jonathanlyon Frequent Visitor
Frequent Visitor

Re: Create totals table from multiple tables

 

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

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 57 members 1,063 guests
Please welcome our newest community members: