Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi guys,
I'm struggling for days on this problem. So I have 2 fact tables (assume that they are linked by all necessary Dimension tables):
The 2 tables have one common Attribute which is "Cost center".
Table A | ||
Cost center | Cost element | USD |
4285200008 | 5006010 | 2,652.00 |
4285200008 | 5012010 | 3,721.00 |
4285200008 | 5012030 | 4,159.00 |
4285200008 | 5014020 | 1,448.00 |
4285200008 | 5014030 | 3,630.00 |
4285200008 | 5020000 | 3,542.00 |
4285200008 | 5024000 | 2,242.00 |
4285200008 | 5026020 | 4,942.00 |
4285210008 | 5006010 | 1,564.00 |
4285210008 | 5012010 | 3,347.00 |
4285210008 | 5012030 | 1,981.00 |
4285210008 | 5014020 | 4,076.00 |
4285210008 | 5014030 | 1,384.00 |
4285210008 | 5020000 | 2,958.00 |
4285210008 | 5024000 | 4,554.00 |
4285210008 | 5026020 | 2,277.00 |
4285220008 | 5006010 | 3,845.00 |
4285220008 | 5012010 | 1,947.00 |
4285220008 | 5012030 | 3,398.00 |
4285220008 | 5014020 | 1,981.00 |
4285220008 | 5014030 | 4,791.00 |
4285220008 | 5020000 | 3,367.00 |
4285220008 | 5024000 | 2,890.00 |
4285220008 | 5026020 | 1,401.00 |
Table B (sorry I've just updated it - the 1st time was wrong) | ||
Cost center | Category | Allocation rate |
4285200008 | Apple | 20% |
4285200008 | Mangle | 50% |
4285200008 | Pearl | 30% |
4285210008 | Apple | 15% |
4285210008 | Mangle | 35% |
4285210008 | Pearl | 50% |
4285220008 | Apple | 22% |
4285220008 | Mangle | 38% |
4285220008 | Pearl | 40% |
(all Cost element in a Cost center have the same allocation rates) |
Desired outcome |
A DAX (preferable, or whichever way works) which can allow users to see how much of each Cost element allocate (USD value) to each Category. |
(this is only sample data, actual data has up to 60 Cost centers, 100 Cost elements and 50 Categories together with other variants such as months) |
Desired outcome for example, Category "Apple"
Cost center | Cost element | Category | Allocation rate | USD Allocated |
4285200008 | 5006010 | Apple | 20% | 530.40 |
4285200008 | 5012010 | Apple | 20% | 744.20 |
4285200008 | 5012030 | Apple | 20% | 831.80 |
4285200008 | 5014020 | Apple | 20% | 289.60 |
4285200008 | 5014030 | Apple | 20% | 726.00 |
4285200008 | 5020000 | Apple | 20% | 708.40 |
4285200008 | 5024000 | Apple | 20% | 448.40 |
4285200008 | 5026020 | Apple | 20% | 988.40 |
4285210008 | 5006010 | Apple | 15% | 234.60 |
4285210008 | 5012010 | Apple | 15% | 502.05 |
4285210008 | 5012030 | Apple | 15% | 297.15 |
4285210008 | 5014020 | Apple | 15% | 611.40 |
4285210008 | 5014030 | Apple | 15% | 207.60 |
4285210008 | 5020000 | Apple | 15% | 443.70 |
4285210008 | 5024000 | Apple | 15% | 683.10 |
4285210008 | 5026020 | Apple | 15% | 341.55 |
4285220008 | 5006010 | Apple | 22% | 845.90 |
4285220008 | 5012010 | Apple | 22% | 428.34 |
4285220008 | 5012030 | Apple | 22% | 747.56 |
4285220008 | 5014020 | Apple | 22% | 435.82 |
4285220008 | 5014030 | Apple | 22% | 1,054.02 |
4285220008 | 5020000 | Apple | 22% | 740.74 |
4285220008 | 5024000 | Apple | 22% | 635.80 |
4285220008 | 5026020 | Apple | 22% | 308.22 |
13,784.75 |
Calculation steps in Excel spreadsheet:
Sorry I don't know how to upload Excel file here.
Thanks so much!!!
Mike
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
Share the exact expected result.
Sorry Table B rate was wrong! I've just corrected it.
So sorry about that.
Mike
Hi,
You may download my PBI file from here.
Hope this helps.
Thank you very much!
I've corrected the allocation table (Table B) - it's my mistake in the first place anyway :). Your data model works excellently!
In my actual data, it's a bit more complex which include other variables such as months, the forecast period (12 months forecast in Jan, 12 months forecasted in Feb, etc.) I use the same approach but I think I was wrong when merging the tables: I use Table A (the $$$ table) not Table B (the rate table) with Left join. So as table A has more duplicates (Forecast period, Months, etc.) so the merging results in a lot of duplicates.
Thank you so much!
Mike
Hi,
Whom are you replying to?
@Ashish_Mathur: I was replying to you, sorry for all the confusion. I marked yours as Solution as well. Thanks again! 🙂
Desired outcome for example, Category "Apple"
Cost center | Cost element | Category | Allocation rate | USD Allocated |
4285200008 | 5006010 | Apple | 20% | 530.40 |
4285200008 | 5012010 | Apple | 20% | 744.20 |
4285200008 | 5012030 | Apple | 20% | 831.80 |
4285200008 | 5014020 | Apple | 20% | 289.60 |
4285200008 | 5014030 | Apple | 20% | 726.00 |
4285200008 | 5020000 | Apple | 20% | 708.40 |
4285200008 | 5024000 | Apple | 20% | 448.40 |
4285200008 | 5026020 | Apple | 20% | 988.40 |
4285210008 | 5006010 | Apple | 15% | 234.60 |
4285210008 | 5012010 | Apple | 15% | 502.05 |
4285210008 | 5012030 | Apple | 15% | 297.15 |
4285210008 | 5014020 | Apple | 15% | 611.40 |
4285210008 | 5014030 | Apple | 15% | 207.60 |
4285210008 | 5020000 | Apple | 15% | 443.70 |
4285210008 | 5024000 | Apple | 15% | 683.10 |
4285210008 | 5026020 | Apple | 15% | 341.55 |
4285220008 | 5006010 | Apple | 22% | 845.90 |
4285220008 | 5012010 | Apple | 22% | 428.34 |
4285220008 | 5012030 | Apple | 22% | 747.56 |
4285220008 | 5014020 | Apple | 22% | 435.82 |
4285220008 | 5014030 | Apple | 22% | 1,054.02 |
4285220008 | 5020000 | Apple | 22% | 740.74 |
4285220008 | 5024000 | Apple | 22% | 635.80 |
4285220008 | 5026020 | Apple | 22% | 308.22 |
13,784.75 |
Thanks so much for simulating a PBI for it. But if it works correctly, the total amount of "Allocation" must be 72,097.00.
By the way, please note I've just edited the Table B rate (it was wrong the first time, so sorry!).
Mike
Hi mikeng, if you have already a connection in your Datamodel use Following Dax using a 'New Measure':
Allocation = sum(TableA[USD]) * max(TableB[Allocation rate])
Greetings
Thanks for your suggestion. I've tested it and it doesn't work.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
75 | |
74 | |
55 | |
45 |
User | Count |
---|---|
117 | |
106 | |
77 | |
66 | |
65 |