Forum Discussion
calculation using 2 fact tables
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 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
Sorry Table B rate was wrong! I've just corrected it.
So sorry about that.
Mike
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
