Forum Discussion
Sum Values from 3 Different Columns
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!
3 Replies
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
Thanks Anonymous 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.
