Forum Discussion
Solved
calculation using 2 fact tables
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):
- Table A: which contains value (USD)
- Table B: allocation rates to Categories
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:
- "Allocation rate": Index Match to find rate from Table B based on Cost center & Category;
- "USD allocated": SumIfs from table A (based on Cost center and Cost element to find USD ammount) and multiply with "Allocation rate"
Sorry I don't know how to upload Excel file here.
Thanks so much!!!
Mike
11 Replies
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.
Hi,
Share the exact expected result.
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 Sorry Table B rate was wrong! I've just corrected it.
So sorry about that.
Mike
