Forum Discussion

ac__sgccck's avatar
ac__sgccck
Frequent Visitor
11 months ago
Solved

Need a quick help: combine table with many-many relationship

Hello all,    I am new to Power BI. As uploaded PBI workbook, I have 2 table: table 1 is sales by cost center, table 2 is cost center %allocation to different unit. what I want to achieve is to hav...
Need Help
  • V-yubandi-msft's avatar
    V-yubandi-msft
    11 months ago

    Hi ac__sgccck ,
    Thank you for engaging with the Microsoft Fabric Community.
    MFelix Thanks for sharing Yes, using SUMX over ADDCOLUMNS (like in your example) is another valid way to solve this In my solution,
    ac__sgccck , I used a different method (LOOKUPVALUE or CALCULATE with TREATAS), but both approaches lead to the same outcome because the main logic is identical-loop through the allocation table, retrieve the matching sales for each CostCenter, multiply by the allocation percentage, and sum the results. Whether you choose ADDCOLUMNS or LOOKUPVALUE/CALCULATE, the final output remains the same. The choice mainly depends on which coding style you find easier to maintain.
    FYI:

     

     

     

    For your reference, I’ve attached the PBIX file. Please check and let me know if any adjustments are needed.

    Regards,
    Yugandhar.