Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Super User

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/

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

 

Why is it not workin?

Example PBIX

2FactTables.png

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

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors