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

[ac__sgccck]

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

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[V-yubandi-msft · Accepted solution]

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.

[ac__sgccck]

Thanks for your solution.

[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
)

[MFelix]

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.