Hello - this is a DAX question.
The am needing to find a way of summing values where the dimension table (Center) is referenced back to itself.
The "CenterSetup" table is compriced of two columns - Center & Allocation. The Allocation field will eventually be a slicer. I do not believe this is a parent child issue - as their is no correlation of centers in the setup.
Center Table (primary)
Center Allocation
| Labor1 | Labor |
| Labor2 | Labor |
| Resource1 | Resource |
| Resource2 | Resource |
| Activity1 | Activity |
| Activity2 | Activity |
| WidgetA | |
| WidgetB | |
This same table was duplicated, and named SourceCenter. Allocation column was removed
SourceCenter (secondary center)
| Labor1 |
| Labor2 |
| Resource1 |
| Resource2 |
| Activity1 |
| Activity2 |
The data table is setup that the Source center provides x number of units to the Target Center.
For example, row 1, labor 1 provides 5 units (hours) of service to Resource1.
DATA TABLE
SourceCenter TargetCenter CDU
| Labor1 | Resource1 | 5 |
| Labor1 | Activity2 | 3 |
| Labor2 | Activity1 | 2 |
| Resource1 | Activity1 | 10 |
| Resource2 | Activity2 | 15 |
| Activity1 | WidgetA | 5 |
| Activity2 | WidgetA | 30 |
The Table relationships
CenterSetup ->Data[TargetCenter]
SourceCenter ->Data[SourceCenter]
Final desired result is a matrix showing for each Center, the number of Target units consumed, and then total number of units tied back to the Source Center. In the top row -Labor 1 -> 8 total units provided.
For Resource1 - 10 units provided, but consuming 5 units Labor1
DESIRED RESULT
| | | |
| Center | SourceCenter | TargetCDU | SourceCDU |
| Labor1 | | | 8 |
| Labor2 | | | 2 |
| Resource1 | | | 10 |
| | Labor1 | 5 | 10 |
| Resource2 | | | 15 |
| Activity1 | | | 5 |
| | Labor2 | 2 | 5 |
| | Resource1 | 10 | 5 |
| Activity2 | | | 30 |
| | Labor1 | 3 | 30 |
| | Resource2 | 15 | 30 |
I am able to accomplish the target CDU column, but the Source CDU is proving difficult!
