How to Sum columns from different tables that have no direct relationship?

[felipeblvieira]

Hello, im putting together the results from 2 different companies.

Company 1 have it own values and info together with their table´s and columns.

The same goes for company 2.

The columns on then dont have any direct relationship...

Now i need to put the dashboard of the whole group, so i need to add the results of total sales from products in company 1 with the products in company 2 and unite then both in a column that is the Total Sales of the Group.

I´ve tryed the easy Sum + command but it only allows to sum columns in the same table, and not in different table like i need.

Can some one please give me some light on this?

Thanks !!

[HarrisMalik]

@felipeblvieira It is modelling question. It really depends how your model is constructed. In dimensional world the measures from different data sources are joined together on conformed dimension. As you explained there are two companies under 1 group selling some products. In this case the time dimension is same for both companies so time/date is a conformed dimension. The group or parent company for both entities is same so it can also be a conformed dimension. You can combine products from both companies into a conformed product dimension. These conformed dimensions are then linked to the two company facts with one to many relationship. The two company facts can be on same grain level or different that doesn't matter, as long as you are exploring them based on conformed dimensions they will give you desired results. 

Please read about dimensional modelling there are many resources available on internet.

Regards

Harris

[fbrossard]

In your model, you should have :

- A company that lists the companies and contains their properties (addresses, number of employees, creation date, bla bla bla....)

- A date table

- And a fact table that combine the results off all the companies you want to analyze.

If you have a result set for each company, you have to combine them with Power Query.

[felipeblvieira]

Thanks a lot for the answer.

But i really need just to create one new column that sums the results of 2 columns in different tables...

No date or other info required right now for this specific target.

I´ve tryed the sumerize ( i think i dunno how it works well but still coudn´t do it for different tables...

The sum command cant do it as well...

[kcantor]

Your measures should work for this. If they do not without a table reference add the table reference into them.

M1 = SUM('Sales1'[Amount])

M2 = SUM('Sales2'[Amount])

Group Total = [M1]+[M2]

If necessary, relate them through your date and product tables but only pull the totals from the facts tables.  This works, not only in theory but in practice as I currently use a version of it myself.

Edit: Sorry, didn't see the above answer.

[Seth_C_Bauer · Accepted solution]

@felipeblvieira If all you need is the total amount for both columns you want a measure.

There may be a better way to do this, but if you create a measure for each total, and a final measure adding those together - that gives you what you are looking for I believe

Measure1 = SUM('table1'[sales])

Measure2 = SUM('table2'[sales])

Total = [Measure1] + [Measure2]

[Motorfinger]

After hours of looking up how to do this using SUM/SUMX and a bunch of other fluff,  this is so simple and exactly what I needed! Thank you!

[Diz]

Hiya,

This is exactly what I am doing (combining sales from 2 sources into a single measure).  My total sales are comprised of product and service sales.   
I would like to use a table visual to display total sales as well as the contributions from each of product and service sales.  This is very straightforward.
Next, I want to enrich the table with additional details from both product and service tables.  There is no relationship between the service data and the product data displayed.  What I would like to see in the visual is a value in the totalsales column plus a value in either the product or service sales column, along with the relevant product or service attributes.  I would like the repeated unrelated values not to appear.

How can I have a table display the details only where they exist and group unrelated data under some form of unrelated identifier?

In other words, if the columns are TotalSales, ProductSales, ProductId, ServiceSales, ServiceId then I would like each row to contain a value for TotalSales plus either ProductSales and ProductId values OR ServiceSales and ServiceId values.

Is this possible?  My data source is a direct queried tabular model so i cannot create new tables within PowerBI.

THanks,

-Diz