cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mikeng
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 centerCost elementUSD
428520000850060102,652.00
428520000850120103,721.00
428520000850120304,159.00
428520000850140201,448.00
428520000850140303,630.00
428520000850200003,542.00
428520000850240002,242.00
428520000850260204,942.00
428521000850060101,564.00
428521000850120103,347.00
428521000850120301,981.00
428521000850140204,076.00
428521000850140301,384.00
428521000850200002,958.00
428521000850240004,554.00
428521000850260202,277.00
428522000850060103,845.00
428522000850120101,947.00
428522000850120303,398.00
428522000850140201,981.00
428522000850140304,791.00
428522000850200003,367.00
428522000850240002,890.00
428522000850260201,401.00

 

Table B (sorry I've just updated it - the 1st time was wrong)  
   
Cost centerCategoryAllocation rate
4285200008Apple20%
4285200008Mangle50%
4285200008Pearl30%
4285210008Apple15%
4285210008Mangle35%
4285210008Pearl50%
4285220008Apple22%
4285220008Mangle38%
4285220008Pearl40%
   
   
   
   
(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 centerCost elementCategoryAllocation rateUSD Allocated
42852000085006010Apple20%530.40
42852000085012010Apple20%744.20
42852000085012030Apple20%831.80
42852000085014020Apple20%289.60
42852000085014030Apple20%726.00
42852000085020000Apple20%708.40
42852000085024000Apple20%448.40
42852000085026020Apple20%988.40
42852100085006010Apple15%234.60
42852100085012010Apple15%502.05
42852100085012030Apple15%297.15
42852100085014020Apple15%611.40
42852100085014030Apple15%207.60
42852100085020000Apple15%443.70
42852100085024000Apple15%683.10
42852100085026020Apple15%341.55
42852200085006010Apple22%845.90
42852200085012010Apple22%428.34
42852200085012030Apple22%747.56
42852200085014020Apple22%435.82
42852200085014030Apple22%1,054.02
42852200085020000Apple22%740.74
42852200085024000Apple22%635.80
42852200085026020Apple22%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

1 ACCEPTED SOLUTION

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
Ashish_Mathur
Super User III
Super User III

Hi,

 

Share the exact expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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!

Power BI model test.JPG

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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 centerCost elementCategoryAllocation rateUSD Allocated
42852000085006010Apple20%530.40
42852000085012010Apple20%744.20
42852000085012030Apple20%831.80
42852000085014020Apple20%289.60
42852000085014030Apple20%726.00
42852000085020000Apple20%708.40
42852000085024000Apple20%448.40
42852000085026020Apple20%988.40
42852100085006010Apple15%234.60
42852100085012010Apple15%502.05
42852100085012030Apple15%297.15
42852100085014020Apple15%611.40
42852100085014030Apple15%207.60
42852100085020000Apple15%443.70
42852100085024000Apple15%683.10
42852100085026020Apple15%341.55
42852200085006010Apple22%845.90
42852200085012010Apple22%428.34
42852200085012030Apple22%747.56
42852200085014020Apple22%435.82
42852200085014030Apple22%1,054.02
42852200085020000Apple22%740.74
42852200085024000Apple22%635.80
42852200085026020Apple22%308.22
    13,784.75

 

Why is it not workin?

Example PBIX

2FactTables.png

mikeng
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

WolfBiber
Microsoft
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

mikeng
Frequent Visitor

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors