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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.