columns from different tables not related (n:m)

[davaru]

Hi Everybody.

I have 2 tables

Table A: ( date, prod_id, costs)

Table B: ( date, prod_id, incomes)

Table C: (prod_id, description)

Relationship TableA , TableC  cardinality Many to Ones (prod_id)

Relationship TableB , TableC  cardinality Many to Ones  (prod_id)

n:m relations between TableA & TableB

I need to resolve gross_margin = incomes - costs by dates

What must I do  ??

wich will be the DAX formulas that need ins measures o calculaed columns ???

Thnks in advance

[v-ljerr-msft · Accepted solution]

Hi @davaru,

I would suggest you to use Merge Queries option in Query Editor to merge Table A and Table B into a single table(let's call it "NewMergedTable") with match columns of "date" and "prod". For more details about how to this, you can refer to this article.

Then you should be able to use the formula below to create a calculate column called "gross_margin" within the "NewMergedTable".

gross_margin = 'NewMergedTable'[incomes] - 'NewMergedTable'[costs]

Regards

[davaru]

Tnks for your response.

[mike_honey]

I would add a "Calendar" table with 1 row per date, e.g. using the CALENDARAUTO function.  Then I would relate that to Table A and B. 

Then the Gross Margin measure would be something like this:

Gross Margin = SUM ( 'Table B'[incomes] )  - SUM ( 'Table A'[costs] )