Sum values from multiple rows and columns by corresponding variables

[Andy_Peak]

I'm struggling with writing my DAX code to return the sum totals I need. Below is a sample data set based on what I'm working with. Based on how the data is structured, I need to return the total savings per facilitator - the totals per facilitator for this example data set are listed above the table. When we have multiple facilitators in a row, the total savings for that row is evenly allocated to each facilitator. Some rows only contain one facilitator, while other rows could have two or even three facilitators. 

Facilitator A = 1700

Facilitator B = 1400

Facilitator C = 1350

Primary Facilitator	Primary Facilitator Savings	Secondary Faciliator	Secondary Facilitator Savings	Tertiary Faciliator	Tertiary Facilitator Savings	Total Savings
Facilitator A	750	Facilitator B	750	Facilitator C	750	2250
Facilitator C	500	Facilitator A	500			1000
Facilitator B	250					250
Facilitator A	50					50
Facilitator C	100	Facilitator B	100	Facilitator A	100	300
Facilitator B	300	Facilitator A	300			600

Thanks!

Andy

[v-yuta-msft · Accepted solution]

Hi Andy_Peak,

To achieve your requirement, duplicate your original table to three tables. Remove columns in the three table as below:

Table1:                                                                            Table2:

 

Table3:

 

Then Append Table1, Table2 and Table3:

 

Finanlly, after applied&closed, create a measure using DAX below:

Result = CALCULATE(SUM(Append1[Facilitator Savings]), ALLEXCEPT(Append1, Append1[Facilitator]))

 

Regards,

Jimmy Tao

[Andy_Peak]

Thanks Jimmy! Worked perfectly.