Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Thanks Jimmy! Worked perfectly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |