SUMIFS DAX equivalent sum same item in different rows and columns

[Mishutka]

I am trying to calculate a sum based a value that could be in one of two columns:

The data is broadly

Date	Customer	Sales value	Salesperson	Sales Assist
01-Jan	Acme Subs	100	John	Jane
05-Jan	Widgets Inc	75	John	Lesley
07-Jan	Acme Subs	125	Jane	John
07-Jan	Widgets Inc	175	Lesley	John

What I want is a sum for each Salesperson or Sales Assistant

Ie total for John should be 100+75+125+150

for Jane should be 100 + 125

for Lesley should be 75 + 175

I have tried sum(table[Sales Value])+calculate(sum(table[Sales Value]),.. but then I cannot get the filter correct. If i say salesperson=sales assist I get nothing (obviously as they arent on the same rows. I have tried ALL, ALLSELECTED, VALUES and cant get anywhere.

Anyone know what DAX formula I need to get the summary results I have shown?

[lbendlin]

Here's a crude way of doing this using a table variable. I am sure there are more elegant solutions

Table 2 = 
var sr = selectcolumns('Table (4)',"Date",'Table (4)'[Date],"Customer",'Table (4)'[Customer],"Sales",'Table (4)'[Sales value],"Salesperson",'Table (4)'[Salesperson])
var sa = selectcolumns('Table (4)',"Date",'Table (4)'[Date],"Customer",'Table (4)'[Customer],"Sales",'Table (4)'[Sales value],"Salesperson",'Table (4)'[Sales Assist])
var combined = union(sr,sa)
return combined

Basically adding the original table back to itself but shifting the assist column to the salesperson column. 

[Mishutka]

Thanks, this will work, although it may be easier to combine the tables in Power Query - especially as thes are quite large data sets and I dont want slow run time performance. I found a really helpful article about variables and context https://www.sqlbi.com/articles/variables-in-dax/ which inspired me to try DAX filtering again. Thus I got to 

var Assister = MAX(Table1[Salesperson])

return
CALCULATE(SUM(Table1[Sales value]),all(Table1[Salesperson]),Table1[Sales Assist]=Assister)

But this gives a slightly odd result: 

Row Labels	Sum of Sales value	AssisterSum
01/01/2020
John	100
05/01/2020
John	75
07/01/2020
Jane	125
Lesley	175
Grand Total	475	75

This is strnage in that it has a grand total, but no line by line values so I am not quite sure what is going on

[Mishutka]

ah, I am getting there, I just need to somehow deal with the dates. If I change all the dates to 7/1 the table now reads

Row Labels	Sum of Sales value	AssisterSum
07/01/2020
Jane	125	100
John	175	300
Lesley	175	75
Grand Total	475	75

The Grand total is wrong becasue of my use of Max, which I will filter out with something like HASONEFILTER, but I need to think about how to deal with dates. I will post my solution here if I get it working

[Mishutka]

Ok, I am nearly there but still havent quite got it. I have updated the table and the measures.

I have a measure 

AssisterSumXX = var Assister = MAX(Table1[Salesperson])
var MinDate = min(Table1[Date])
var MaxDate = max(Table1[Date])
return
if(HASONEFILTER(Table1[Salesperson]),CALCULATE(SUM(Table1[Sales value])+0,all(Table1[Salesperson]),

Table1[Sales Assist]=Assister),SUM(Table1[Sales value]))

And in order to try to force a value for each row, I have another SalesX=[Sum of Sales value]+0 and finally Total Attributable Sales=[SalesX]+[AssisterSumXX]

Here are the data table and pivot table output.

As you can see, the Grand Totals are correct, but the row and row totals are wrong for 8/1 where John has no Sales, but a Sales Assist - he should show 107 under AssisterSumXX and the row total for John should also be 107. Even though this shows zero, the totals are correct.  Anyone got any ideas to close this thing down? I have tried various attempts at Values and adding zero but to no avail.  All help really gratefully received