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