Forum Discussion
joshua1990
Post Prodigy
Post ProdigyCalculated column with data from not related table
Hi all!
Ich have 2 transactional tables und two dimensional tables
Transactional 1: Sales per Article per Week
Transactional 2: Orders per Article per Week
Dimensional 1: Table of all Artic...
NewColumn=VAR _week=RELATED(Calendar[Week]) VAR _article=RELATED(Dimensional1[Article]) RETURN COUNTROWS(FILTER(Transaction2,Transaction2[Week]=_week&&Transaction2[Article]=_article))
olgad
Resident Rockstar
Resident RockstarHi there,
what you could try, create a primary key on both tables using concatenate of article and date/week field
PrKey = Orders[ArticleID]&"-"&Orders[Date]
PrKey = Sales[ArticleID]&"-"&Orders[Date]
PrKey = Sales[ArticleID]&"-"&Orders[Date]
Then create a relationship between the Orders and Sales on that key ( it will be an inactive one)
Then on sales table, create a column:
Calculate(Sum(Orders[Order Quantity]), USERELATIONSHIP(Orders[PrKey], Sales[PrKey]))
If your relationship will be many to many between sales and orders, then you will get a sum of orders, if inittially aour tables are aggregates per week, then you have what you wanted.
Customer 1 Article 1 Week 1 500 Revenue 200 orders
Customer 1 Article 1 Week 1 500 Revenue 200 orders
Please let me know if that helped.
Customer 2 Article 1 Week 1 200 Revenue 200 orders
Customer 2 Article 1 Week 1 200 Revenue 200 orders