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.
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!
Solved! Go to Solution.
In the query editor try adding a conditional column. In the dialog, IF CC > 0 Then CC Else Select a Column -> Derived CC
In the query editor try adding a conditional column. In the dialog, IF CC > 0 Then CC Else Select a Column -> Derived CC
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |