Sum Values from 3 Different Columns

[rjh01015]

Our business process grants that sales orders can be split 3 ways and assigned to 3 different people. My users wants to see the sum of their "splits" as a single value but I am really struggling with finding a way to achieve this.

I have 6 columns that I need to sum data from: 3 name columns and 3 amount columns. Essentially I need to find a way to look for a name in columns Name1, Name2 and Name3, then add the related values from Amount1, Amount2 and Amount3.

If anyone can help with this problem I've been trying to resolve for a really long time, I would be very greateful! 

[Corey_M]

if I understood correctly that you are wanting it so that it only counts amount 3 for the name 3.

So in this sample data the total for A would be 1 (from row1 amount1)+ 4 (from row 3 amount 1) + 1 (from row4 amount2) and + 7 (row5 amount3) for a total of 13

create a table for the distinct names (you likely already have one, but if you don't)

Distinct Names = 
DISTINCT(
    UNION(
        SELECTCOLUMNS(Sales, "Name", Sales[Name1]),
        SELECTCOLUMNS(Sales, "Name", Sales[Name2]),
        SELECTCOLUMNS(Sales, "Name", Sales[Name3])

))

Next you want to make your sales table easier to calculate upon by creating a table where it basically splits each name:amount pairing into its own row like so

Combined Names and Amounts = 
UNION(
    SELECTCOLUMNS(Sales, "Name", Sales[Name1], "Amount", Sales[Amount1]),
    SELECTCOLUMNS(Sales, "Name", Sales[Name2], "Amount", Sales[Amount2]),
    SELECTCOLUMNS(Sales, "Name", Sales[Name3], "Amount", Sales[Amount3])
)

  And finally your total measure 

Total Amount Per Name = 
SUMX(
    SUMMARIZE('Combined Names and Amounts', [Name], "TotalAmount", SUM([Amount])),
    [TotalAmount]
)

then just create a relationship between the Combined Names and Amounts Name column and the distinct names Name column and you are good to go

[rjh01015]

Thanks @Corey_M that got me part of the way there and much easier than the alternate solution I just came up with.

What I also need however, is to filter based on a date field in the source data set. I created a relationship between the "Name" in the source data set, to the Name in Combined Names table and it won't filter on month/quarter from the source data.

[Corey_M · Accepted solution]

if the date is in your sales table just include it in the selected columns when creating combined accounts and names table and either filter directly on the date in the combined accounts and names or create a relationship between it and your sales table and filter on the sales table date.