Multiplying between Tables

[inescosta]

Hello,

I am having problems with these tables. I need to calculate the (af_escalaItem[valor]*af_servprod_criterios[peso]) for each af_aval_id, but I just can't seem to do this.

Thank you in advance

[Greg_Deckler]

It looks like you might be missing some relationships between tables and you may need some "helper" tables.

So, for example, you would need a table with unique "escala_id" values I think and relate all of your tables with "escala_id" columns to this table. Then, you probably need a table with unique "af_servprod_id" values and relate all of your tables with with "af_servprod_id" columns to this table. Then you would need a table that relates "af_servprod_id"'s to "escala_id"'s. Then, you should be able to create a measure or a custom column in your table with unique "escala_id"'s to do the calculation.

[inescosta]

I thought I had those relationships, here is a print of the relationships I made. Do you think there is any missing?

Thank you

[v-ljerr-msft]

Hi @inescosta,

According to the screen shoot of your table relationships above, you should be able to use the formula below to create a new calculate column in "af_avaliacaoItens" table to to calculate the (af_escalaItem[valor]*af_servprod_criterios[peso]) for each af_aval_id.

NewCalculateColumn = RELATED ( af_escalaItem[valor] ) * SUMX ( RELATEDTABLE ( af_servprod_criterios ), af_servprod_criterios[peso] )

Regards

[inescosta · Accepted solution]

Hi @v-ljerr-msft,

Thank you so much for the help but using the formula sumx returns the sum of all the values but that is not what I intended. I think this is my solution:

column=RELATED(af_escalaItem[valor]) *

CALCULATE(

    VALUES(af_servprod_criterios[peso]),

    FILTER(

        af_servprod_criterios,

        af_avaliacaoItens[af_criterio_id] = af_servprod_criterios[af_criterio_id] &&

        RELATED(af_fornecedorAvalia[af_servprod_id]) = af_servprod_criterios[af_servprod_id]