calculation using 2 fact tables

[mikeng]

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

[Ashish_Mathur]

Hi,

Share the exact expected result.

[mikeng]

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

So sorry about that.

Mike

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[mikeng]

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

[Ashish_Mathur]

Hi,

Whom are you replying to?

[mikeng]

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

[mikeng]

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

[WolfBiber]

Why is it not workin?

Example PBIX

[mikeng]

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

[WolfBiber]

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

[mikeng]

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