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 have a master data from accounting where one column is cost center. Then I have another table for explaining the data centers and another information, like locations etc. in it. We call it the key table. The same table also has another dimensions that needs to be explained so the column cost center actually has another things too. Like city codes and industry codes. But to simplify the tables are something like this:
Amount | Cost center | Cost Center | Name | ||
150 | A | A | John | ||
400 | A | B | Liz | ||
200 | B | C | Jake | ||
50 | D | D | Sarah | ||
-70 | E | E | Kate | ||
700 | B | ||||
10 | C |
What I would need to do is split some cost centers to two different cost centers 50/50. Lets say A needs to be divided between B and C.
I don't know where to go from here, I'm quite new in Power bi 🙂 I was thinking adding columns to the key data explaining what cost centers should be used instead of the original cost center. Cost centers that needs to be reallocated are pretty rare (maybe 15-20 at this time) so it is not a big job to maintain this table. Like this:
Cost Center | Name | Reallocating 1 | Reallocting 2 |
A | John | B | C |
B | Liz | ||
C | Jake | ||
D | Sarah | E | B |
E | Kate |
And then some formula to make the 50 % allocating. Maybe a IF formula altough it seems a bit complicated. If Columns C and D have something, allocate the sum to these cost centers instead. Any ideas are much appreciated 🙂
It took me way too much time to wonder how to attach files here. And then realize it's not bossible.
Here is a link to Drive where the example data tables are
What I am looking for is a way to divide sums for certain cost centres. So if the data has a row with the cost center J1, that sum should be allocated to 50 % to T1 and 50 % to T2. If it's J2 then 50 % to T2 and 50 % to T9 and so on. It's alwas split even to two different cost centers. For example
Hi @Suna ,
>>What I would need to do is split some cost centers to two different cost centers 50/50. Lets say A needs to be divided between B and C.
Could you clarify what's the splitting logic ?
I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.
Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Are you able to provide a view of your data model and some sample data from each of your tables, or better a sample PBIX file.
Proud to be a Super User!
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |