cancel
Showing results for
Did you mean:
Frequent Visitor

## 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

• "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

1 ACCEPTED SOLUTION
Super User III

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
11 REPLIES 11
Super User III

Hi,

Share the exact expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Sorry Table B rate was wrong! I've just corrected it.

Mike

Super User III

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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

Super User III

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

@Ashish_Mathur: I was replying to you, sorry for all the confusion. I marked yours as Solution as well. Thanks again! 🙂

Frequent Visitor

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
Microsoft

Why is it not workin?

Example PBIX

Frequent Visitor

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

Microsoft

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

Frequent Visitor

Thanks for your suggestion. I've tested it and it doesn't work.

Announcements