relationship between 2 fact tables

[rajanimaddala]

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

[v-eachen-msft · Accepted solution]

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.