Forum Discussion
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 have a table 3 to show unit->cost center->allocated sale, where allocated sale = sales amount * % allocation.
In the PBi workbook, I joined the 2 tables by using a bridging table to avoid many-to-many relationship. I can get correct allocated sales by unit, however, the toal amount does not match up with the rows.
Any suggestion would be appreciated!
link to the pbix file:
https://drive.google.com/file/d/1kMBIDxUZeq7wQ6802RuZ3NDozneQ_Q2B/view?usp=drivesdk
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.
5 Replies
- MFelix
Super User
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.
- mh2587
Allocated Sale = --Try this might help you SUMX( -- get unique unit × cost-center allocation rows from your allocation table SUMMARIZE( 'AllocTable', -- your allocation table (table2) 'AllocTable'[Unit], 'AllocTable'[CostCenter], "AllocPct", SUM( 'AllocTable'[AllocationPct] ) ), VAR cc = [CostCenter] VAR alloc = [AllocPct] -- get the sales for that cost center from the sales table (table1) VAR sales = CALCULATE( SUM( 'SalesTable'[SalesAmount] ), -- your sales column ALL( 'AllocTable' ), -- remove allocation table filter duplication risk 'SalesTable'[CostCenter] = cc ) RETURN sales * alloc ) 
V-yubandi-msftCommunity Support
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.Thanks for your solution.
- Ashish_Mathur
Hi,
PBI file attached.
Hope this helps.
