Need help on calculate a measure from 2 different tables

[Anonymous]

Hi,

I have table like below:

Table1:

M1	M2	P1	P2	Stock
zzzzzz	Japan	C2345	01/05/2019	55
zzzzzz	Japan	C2345	01/06/2019	20
zzzzzz	Japan	C2345	01/07/2019	45
zzzzzz	Japan	C2345	01/08/2019	24
zzzzzz	India	C2345	01/05/2019	12
zzzzzz	India	C2345	01/06/2019	0
zzzzzz	India	C2345	01/07/2019	67
zzzzzz	India	C2345	01/08/2019	99

Table2 :

M1	M2	P1	P2	Sales
zzzzzz	Japan	C2345	01/05/2019	10
zzzzzz	Japan	C2345	01/06/2019	0
zzzzzz	Japan	C2345	01/07/2019	21
zzzzzz	Japan	C2345	01/08/2019	11
zzzzzz	India	C2345	01/05/2019	2
zzzzzz	India	C2345	01/06/2019	0
zzzzzz	India	C2345	01/07/2019	9
zzzzzz	India	C2345	01/08/2019	33

in above 2 tables, columns M1,M2,P1,P2 values are almost same, so I want to calculate new calc measure from these 2 tables like below:

			January	Febrauary	March	April	May	June	July	Augest	Septemper	October	November	December
zzzzzz	Japan	C2345	0	0	0	0	45	20	24	13	0	0	0	0
zzzzzz	India	C2345	0	0	0	0	10	0	58	66	0	0	0	0

 for the data available months, it should calculate a new measure = stock - sales.

I tried to achoeve using the RELATED function is not worked. excep data columns all other columns are same in both the tables can anyone suggest the way to relate those tables and calculate the new measures.

Thank you.

[Anonymous · Accepted solution]

HI @Anonymous ,

I'd like to suggest you create a calculated column to concatenate multiple fields, then you can create a table to extract two table concatenate column values and use it as bridge to link two tables:

Relationship in Power BI with Multiple Columns 

After these, you can simply create measure to get correspond stock amount and sales amount for calculating.

measure =
SUM ( table1[stock] ) - SUM ( table2[sales] )

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you so much Xiaoxin.  Its worked now.

[Mariusz]

Hi @Anonymous 

You can create dimensions like in the article below.

https://radacad.com/basics-of-modeling-in-power-bi-what-is-a-dimension-table-and-why-say-no-to-a-single-big-table

And create relationships between newly created dimensions and Both of your (fact) tables.

This will allow you to yous dimension tables in your report and propagate filters to both tables at the same time.

Another option would be using a TREATAS() DAX expression in one of the Measures

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.