I have 2 FACT TABLES that have shared Dimesion tables as the Diagram.
factWidget Contains the Number of Widgets that have been sold by Type, Dataset, Date and Region and factMoney has a single line for cost for each Dataset, Date and Region combination so it is a bit like a header detail senario.
How do I relate or model the 2 tables so that I can have them on the same pivot? Id rather not flatten them as I cannot get hold of the Line Costs.
An example is where I might want to SLICE BY a Date of today with a region of Europe and Dataset to create a Matrix or Table that gives me :-
05/03/2018 Europe £1000 (This is the Value from factMoney) Big (Type from Fact Widget) 5000 (Widget Total Sold from factWidgets)
05/03/2018 North America £1090 (This is the Value from factMoney) Medium (Type from Fact Widget) 2000 (Widget Total Sold from factWidgets)
05/03/2018 New Zealand £10090 (This is the Value from factMoney) Small (Type from Fact Widget) 2000 (Widget Total Sold from factWidgets)
Another Scenarion might be:-
Date Region Small Medium Big Total
05/03/2018 North America 500 400 200 £1080
Hopefully this makes sense, I have been fighting the beast all day and have just hit the DAX Wall so any help gratefully received.