Best Way to Merge Data?

[Anonymous]

I have two Salesforce objects that I'm connected to. They are Delta Account which looks like this:

And Client Revenue Which looks like this:



Both tables are related by the columns that I've highlighted in each screenshot. What I'm trying to achieve is getting the revenue from Client Revenue into the Delta Account table. Problem is as you can see in the Client Revenue object, rather than having the total for each company it has individual revenue amounts so PBI throws an error saying it can't match them because one of the tables must have unique values.

From there I then took the Client Revenue object, and grouped it by the Related_Account_c field and aggregated it by summing the revenue so I got each Related_Account_c ID once with it's corresponding revenue total, which looks like this:



I was then allowed to create a relationship between Delta Account and Client Revenue. I was then able to use fields from both tables and insert them into the same table which came out like this, which was what I was initially looking for:





I then simply wanted to display the total at the bottom of this table and this happens:

What this is doing is giving me the total sum of all lines of revenue from my Client Revenue object. The total even includes revenue from companies not contained within the particular table above. It's giving me everything even though the above is supposed to be a filtered table looking at a certain set of companies. What can I do to fix this? I simply want the total revenue for each company by line and the grand total at the bottom, based upon what's in my table.

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Please create the following column in  your Delta Account table. Then drag the column to your table visual and you will get expected result.

Column = RELATED('Client Revenue'[Revenue])

Regards,
Lydia