Forum Discussion

rajanimaddala's avatar
rajanimaddala
Icon for Helper II rankHelper II
7 years ago
Solved

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, ...
Need Help
Tips and Tricks
  • v-eachen-msft's avatar
    v-eachen-msft
    7 years ago

    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.

     

v-eachen-msft's avatar
v-eachen-msft
Icon for Community Support rankCommunity Support
7 years ago

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.