Forum Discussion

savanahubio's avatar
savanahubio
Frequent Visitor
8 years ago
Solved

Relationship between 2 tables

Hello,   I am trying to look at Sales versus Sales Forecast. My sales Table is daily data by item.  My Forecast table is by week and item.    How can I create a relationship so I can look at...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi savanahubio,

     

    Based on my test, you should be able to use the formula below to create a new measure to calculate the forecast value, then show the created measure as Line Values on the chart to get the expected result without creating any relationships in your scenario.

    Measure = 
CALCULATE (
    SUM ( 'POS Forecast'[POS Forecast] ),
    FILTER (
        'POS Forecast',
        CONTAINS (
            'Daily Sales by Item',
            'Daily Sales by Item'[Item Description], 'POS Forecast'[Item Description]
        )
            && 'POS Forecast'[Year] = MAX ( 'Daily Sales by Item'[Year] )
            && 'POS Forecast'[Week Num] = MAX ( 'Daily Sales by Item'[Week Num] )
    )
)

     

    Here is the modified pbix file for your reference. :smileyhappy:

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

Hi savanahubio,

 

There is only one table in your shared pbix file. Could you also shared the other table, so that I can try to find a way to create the relationship for you? :smileyhappy:

 

 

Regards

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi savanahubio,

     

    Based on my test, you should be able to use the formula below to create a new measure to calculate the forecast value, then show the created measure as Line Values on the chart to get the expected result without creating any relationships in your scenario.

    Measure = 
CALCULATE (
    SUM ( 'POS Forecast'[POS Forecast] ),
    FILTER (
        'POS Forecast',
        CONTAINS (
            'Daily Sales by Item',
            'Daily Sales by Item'[Item Description], 'POS Forecast'[Item Description]
        )
            && 'POS Forecast'[Year] = MAX ( 'Daily Sales by Item'[Year] )
            && 'POS Forecast'[Week Num] = MAX ( 'Daily Sales by Item'[Week Num] )
    )
)

     

    Here is the modified pbix file for your reference. :smileyhappy:

     

    Regards