Matrix Data - Consolidate Columns in to Row

Right now it looks like you have 2 different tables. A lead table and a Member table, each with the first column holding a persons name.  I would recommend creating a 3rd table to hold all of your people. This would be a table with 1 row per person. You join this person table to both the LEAD and MEMBER tables.

With this table relationship set up, you can use the Person name from this newly created person table, then you bring in the lead numerical column and the member numerical column.  Set the aggregation to SUM.  The table/matrix visual can produce a total for you.

You might be wondering how to create a person table.  If you have that in one of your sources, use that first.  If not, you can reference a query inside "Transform Data" to create a new table.  You can select a single column and say "Remove Other Columns" then tell it to "Remove Duplicates".  You can also set a query so that its "Enable Load" is false and it will be temporary table for transforming.  The reason I say this is that you do the above steps separately for each query, giving you 2 distinct lists.  Then use the "append as new" function to combine the results in a new table that you will load into your model.  As long as both columns have the same name, it will work.  If not, rename them after removing duplicates.  You will likely have duplicates after appending, but you can do remove duplicates a second time.

Hi jlestervas ,

Thanks Anonymous  for the quick reply and solution. I have some other ideas:

(1) My test data is the same as yours.

(2) We can create two tables.

Lead Table = SELECTCOLUMNS('Table',"Lead",[Lead],"Lead Allocation",[Lead Allocation]) 

Member Table = SELECTCOLUMNS('Table',"Member",[Member],"Member Allocation",[Member Allocation]) 

(3) We can create the model relationship.

(4) We can create a measure. 

Total = MAX('Lead Table'[Lead Allocation])+MAX('Member Table'[Member Allocation])

(5) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.