Compare text columns in two different tables and sum valor

[Anonymous]

Hy.

I need sum field valor in this condition.

IF TABLE A[NR_IT_PED ] = TABBLE B[NR_IT_PED ] SUM TABLE A[VALOR]

Some confuse, but in SQL would look like this I think

SELECT SUM(B.QTD * A.VALOR) AS VALOR FROM
(SELECT VALOR, NR_IT_PED FROM PROJ_DOC_CONTABIL
WHERE CTA_RAZAO ='0151050003' OR CTA_RAZAO ='0151050002') AS A
OUTER APPLY (
SELECT COUNT(*) AS QTD FROM PROJ_PEDIDO
WHERE NR_IT_PED = A.NR_IT_PED) AS B

[vanessafvg]

@Anonymous can you give an example of what you data looks like?

[Anonymous]

IF TABLE A[NR_IT_PED] = TABLE B[NR_IT_PED]

valor = valor + valor

sum only where the fields have the same values

[vanessafvg]

@Anonymous are you comparing row order  or just nr_it_pd  or  nr_it_ped?

because if you had to sum everything in b that is in A you would get 110 not 90

[Anonymous]

[vanessafvg · Accepted solution]

@Anonymous so what you need to do in my opinion is

1. you need to create a relationship between the tables in order to do that,  and you will do that in the relationship view.  you first either need to create a bridging table with the unique list of nr_it_pd from table b, or you could just deduplicate in table b  power query if thats what you want to do, removing all the duplicate numbers (you have to do this becasue it wont create a relationship between the two tables if there is more than one row with the same value)

2 then you can create a measure which looks something like this

measure =
CALCULATE ( SUM ( tablea[valor] ), RELATEDTABLE ( tableb ) )

[Anonymous]

Are you right 110

forget number 5

[Anonymous]

Sorry both is NR_IT_PED

don't exist NR_IT_PD