Subtract rows from a table based on conditions indicated in another table

[Syndicate_Admin]

Good morning.

I have a table of transactions of different companies, these can make contracts between them (contracts table). I need to construct a table that shows me the result of these contracts as a difference of transactions between the buyer and seller (result table), as shown in the following image. Thanks for the support.

[Anonymous · Accepted solution]

Hi @rpachecol ,

Please try to create the following measure.

VALOR* =
SWITCH (
    MAX ( 'CONTRATOS'[CONTRATO] ),
    "EMPRESA 1-EMPRESA 2",
        CALCULATE (
            SUM ( 'TRANSACCIONES'[VALOR] ),
            FILTER ( 'TRANSACCIONES', [EMPRESA] = "EMPRESA 1" )
        )
            - CALCULATE (
                SUM ( 'TRANSACCIONES'[VALOR] ),
                FILTER ( 'TRANSACCIONES', [EMPRESA] = "EMPRESA 2" )
            ),
    "EMPRESA 2-EMPRESA 4",
        CALCULATE (
            SUM ( 'TRANSACCIONES'[VALOR] ),
            FILTER ( 'TRANSACCIONES', [EMPRESA] = "EMPRESA 2" )
        )
            - CALCULATE (
                SUM ( 'TRANSACCIONES'[VALOR] ),
                FILTER ( 'TRANSACCIONES', [EMPRESA] = "EMPRESA 4" )
            )
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[freginier]

You need to merge your data in power query

Two merges In Contract table add valor :

First merge to add Comprador Valor (link comprador - empresa)  

Second merge to add Vendedor Valor (link vendedor - empresa)

Then you should be able to substract values.