Forum Discussion
relationship between 2 fact tables
Hi mates
I have 2 fact tables
Sales
Targets
Common field between 2 tables is only ProductGroup but is many to many
I need to display table visual to show
Month, Year, Product Group, Target, Total_Sales
How do I relate these 2 tables and create measure to show as above
Hi rajanimaddala ,
Here are my sample tables.
Use “Left outer” to merge these two tables.
Add three new columns.
Year = YEAR ( Merge1[Date] )
Month = MONTH ( Merge1[Date] )
Column = IF ( Merge1[Year] * 100 + Merge1[Month] = Merge1[Targets.YM], TRUE (), FALSE () )Create a new table to make visual.
FinalTable = CALCULATETABLE ( Merge1, Merge1[Column] = TRUE () )
Add a new measure “TotalSales”.
TotalSales = CALCULATE ( SUM ( 'FinalTable'[Sales] ), ALLEXCEPT ( 'FinalTable', 'FinalTable'[Targets.YM], 'FinalTable'[Product Group] ) )According to my ideas to get the results you want.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi rajanimaddala ,
Here are my sample tables.
Use “Left outer” to merge these two tables.
Add three new columns.
Year = YEAR ( Merge1[Date] )
Month = MONTH ( Merge1[Date] )
Column = IF ( Merge1[Year] * 100 + Merge1[Month] = Merge1[Targets.YM], TRUE (), FALSE () )Create a new table to make visual.
FinalTable = CALCULATETABLE ( Merge1, Merge1[Column] = TRUE () )
Add a new measure “TotalSales”.
TotalSales = CALCULATE ( SUM ( 'FinalTable'[Sales] ), ALLEXCEPT ( 'FinalTable', 'FinalTable'[Targets.YM], 'FinalTable'[Product Group] ) )According to my ideas to get the results you want.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.