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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

 

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
Employee
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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.