Forum Discussion
Merge data from two columns into one
Background: Our transactions are booked using either project codes or cost center (CC) (not both). Ultimately the project codes roll to specific CC. So I want to be able to show a total spend by CC regardless of it's project or CC based. The best I can do with a measure is to show results booked directly to a CC, and the next column over show results with a derived CC from the project. How can I get it into one?
The best I can do is this result. How can I get this back in one CC column? The 75 and 225 represents the spend directly to projects, but I'd like the derived CC to be in-line with the CC.
| CC | Amount | Derived CC |
| 75 | 4707 | |
| 225 | 4708 | |
| 4707 | 100 | |
| 4708 | 35 | |
| 4709 | 40 | |
| 475 |
My sample dataset:
Date Account CC Project Amount Text
| 1/1/2019 | 642801 | 4707 | 100 | Entry 1 of 7 | |
| 1/15/2019 | 642901 | DD-4-11-0006 | 50 | Entry 2 of 7 | |
| 1/25/2019 | 642703 | DD-4-01-0018 | 25 | Entry 3 of 7 | |
| 2/1/2019 | 643303 | 4708 | 35 | Entry 4 of 7 | |
| 3/5/2019 | 643701 | 4709 | 40 | Entry 5 of 7 | |
| 3/15/2019 | 642801 | MG-4-01-0001-01 | 150 | Entry 6 of 7 | |
| 3/31/2019 | 642801 | MG-4-01-0001-01-01 | 75 | Entry 7 of 7 |
475
Mapping of projects to CC. Then this table is linked to my cost center look-up table on the CC number column.
Dimension Project Number Description CC
| Project | DD-4-01 | X1 | 4707 | |
| Project | DD-4-02 | X2 | 4707 | |
| Project | MG-4-01 | X3 | 4708 |
Thanks!
