cumulative total based on text field

[FuzzySteve]

Hello Everyone,  I have a feeling this will be a pretty easy question for someone out there.

I've got some search term data by month and I want to return a total across all data in the separate tables.  

Table 1

Term         count

Apples      10

Oranges    5

Grapes      7

table 2

Term        Count

Oranges   15

Grapes      6

Pears        3

what I would like to visualize on is a total per term. In this example, it would be Oranges 20, Grapes 13, apples 10, and Pears 3.

Can anyone start me on the right path to get this solved?  Thanks!

[parry2k]

you can create new calculated table in dax to union these tables or append these table using query editor.

[FuzzySteve]

Ran a merge and it worked for entries that existed in table 1 but didn't append the new entries into table 2.  (so pears was left out of the resulting table.) 

half way there.  Will keep looking.

[parry2k · Accepted solution]

hmmm not sure why you are doing merge, you should be using append, at end of the day, you will get one table that will something like this:

Table 1

Term         count

Apples      10

Oranges    5

Grapes      7

Oranges   15

Grapes      6

Pears        3

now in your table visual, put two column in value:

term and count, and use sum aggregation for count and that will give you the result.

**Don't use merge, append please

[v-ljerr-msft]

Hi @FuzzySteve,

Have you tried the solution provided by @parry2k above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here. 

Regards