Forum Discussion

Andy_Peak's avatar
Andy_Peak
New Member
8 years ago
Solved

Sum values from multiple rows and columns by corresponding variables

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 FacilitatorPrimary Facilitator SavingsSecondary FaciliatorSecondary Facilitator SavingsTertiary FaciliatorTertiary Facilitator SavingsTotal Savings
Facilitator A750Facilitator B750Facilitator C7502250
Facilitator C500Facilitator A500  1000
Facilitator B250    250
Facilitator A50    50
Facilitator C100Facilitator B100Facilitator A100300
Facilitator B300Facilitator A300  600

 

Thanks!

Andy

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    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

     

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    8 years ago

    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