Forum Discussion

mikeng's avatar
mikeng
Frequent Visitor
7 years ago
Solved

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 ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    You may download my PBI file from here.

     

    Hope this helps.

     

mikeng's avatar
mikeng
Frequent Visitor
7 years ago

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
  • mikeng's avatar
    mikeng
    Frequent Visitor
    7 years ago

    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