Forum Discussion

ac__sgccck's avatar
ac__sgccck
Frequent Visitor
1 year 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
    1 year 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.

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
1 year ago

Hi ac__sgccck ,

 

You need to do a SUMX to get the correct amount redo the measure to:

_allocated sale = SUMX(ADDCOLUMNS(Table2, "TotalSales", [sum of sale]),[TotalSales] * Table2[allocation])

 

Has you can see the final result is correct:

 

Basically we are creating a table that has all the values for sales based on the cost center and then multiplying that by the allocation:

With this table we do the SUM for each of the values.