Issue calculating values with a "from - to" table

[Anonymous]

Hello I've been stuck on this for the past 2 days and I hardly can describe whats goiing on but let me try anyway...

I have 3 tables conected as this, "Venda" is my fact table with my sales records, "produto" is one dimension table describing what category my products are and "produto_espelho" is my "from to table" that relates product A (from "produtos"with a similar product B (also from "produtos" based on their product codes.

The "Vendas" table can e summarized on the fields that I need for this task as the product id (id interno produto), sales quantity(venda quantidade) and gross sale (venda bruta)

 My product table is as follows, product id(id interno produto),, product description (descricao_produto), a column to mark items that are  produced by us (flag marca própria) and the brand column (Brand)

And the "produto espelho" table (from - to) that relates the products aboce with similar items FROM the sabe table, where mp_ cod and cod target are values fom id_interno produto from the previous table. The goal of this table is to check a record and give me back the product code from a similar item from the table (i.e chips from brand A , which have a code XXXX is similar to chips from brand B that has a code YYYY)

Alright, now in PBI I want to create a table view like so:

So basically for each product code that contains the flag MP (from product table) it calculates the sum of "venda bruta" from my sales table "venda" and return it as  "total sales MP", same for the # of sales, then it checks my "produto espelho" (from  - to table) for the code that corresponds to a similar item (in the example above  457323 is similar to 1099255.

Gives me back that target code (from produto espelho table) and then proceeds to calculate the sum of sales and # of sales based on that code (both calculations are to be made baed on the "venda" table).

What I could work out were the total sales MP and sales quantity MP:

Total sales MP = CALCULATE(SUM('Venda'[venda bruta]),'Produto'[flag_marca_propria] IN { 1 })

Sales quantity MP = CALCULATE(SUM('Venda'[venda_quantidade]),'Produto'[flag_marca_propria] IN { 1 })

I checked the results and they are correct, however I cannot write expressions to calculate the target values (total sales target, and sales quantity target), they either return the same values as the above expressions  or simply do not work at all,Ive searched around but I am not familiarized how to use related, or cross filter expressions to mke this work.

Can some one give me a hand ? I am going crazy over this.

[Anonymous · Accepted solution]

Managed to get it to work, it happnes that chaning the data structure does not reflect imediatly on dax formulas... só I was getting a circular relationsjip error but even when chaning the data structure the error would not disappear.

So I changed it, copied and deleted my formula, pasted it and the error dissapeared.

[lbendlin]

since there is an active 1:1 relationship between produto and produto_espelho - what would happen if you would merge these two tables into one?

[Anonymous]

I,ve tried that however it stil did not work. The issue I believe is that I am adding values from table "vendas" but for some itemas I need a condition to check table "produto espelho"  if there is a correspondency of a given product id then I go back to table vendas and add the value not of the original product id, but the related one.

Something like this: