Merging Tables with Addition Function

[jimbolya]

I have two tables. They both have two columns. A category column and then a count for that category.

Example:
Table #1

Column 1      Column 2
Abuse            45

Table #2

Column 1      Column 2
Abuse           3

I want to join these two tables so that the category is summed.
In this example Abuse would be summed at 48.

Any non-matching rows would simply get their own row and count.

So in this example there is clearly two rows between the two tables with a category of Abuse.
However, maybe there’s a row category of ‘Antagonism’ that doesn’t have any other match. It gets its own row.

Jimbo

[anandav · Accepted solution]

@jimbolya,

Try appending the two tables.

Go to Home - Edit Queries - Append Queries - Append Queries as New.

If you add the Colum 1 and Column 2 to a Table visual, it should display the summarised values.

If you need to create a data table of the summarised values, you can use the SUMMARIZE() DAX function.

If this solves your problem please mark as teh solution.

[jimbolya]

I will try this.  

However, I've never used the SUMARIZE DAX function...

That part will probaby not go well lol. 

Jimbo

[NipponSahore]

@jimbolya

I would do the following :

1.  Append the two tables together
2.  Group the resultant by Column 1 and calculate the sum of Column 2 

[jimbolya]

Nope - abuse is still 45...needs to be 48 in order for me to know the operation worked...

[NipponSahore]

Should not be. Do you still see 2 Abuse after grouping the data?

[jimbolya]

So I was able to upload an image...

Clearly looking at the image Abuse shoudl have 48 as the total...

Jimbo

[jimbolya]

[NipponSahore]

Please make sure that the total number of columns are the same and have same names and Datatypes. As the 2nd column in the 1st table is a alphabet and not a number, the append did not take place. Also when you append the data correctly, the total number of columns remains 2

[jimbolya]

I wish I could post an image...

If I do a basic append of the tables I get 4 total columns...

Table 1 + Table 2