Home Forums Power Pivot Joining table not working

Tagged: ,

This topic contains 9 replies, has 2 voices, and was last updated by  tomallan 4 days, 15 hours ago.

Viewing 10 posts - 1 through 10 (of 10 total)
  • Author
    Posts
  • #10590

    jhd
    Participant
    • Started: 1
    • Replies: 5
    • Total: 6

    I have 3 tables; calendar, sales, forecast

    I want to join all 3 tables so that I can produce pivot tables that show actual sales for past years and forecast sales for future years. Sales occur on a daily basis, but we forecast sales annually.

    I can connect the date column in the sales tab to the date column in the calendar table.

    I can’t connect the year column in the forecast table to the year column in the calendar column because of ‘many to many’.

    I have tried to solve this by creating an additional ‘joining’ table that contains a single column; year, and connecting it to the forecast and calendar sales.

    However, this is not working. Pivot tables based on the calendar, sales and forecast tables show the total grand total for the forecast rather than splitting it by year. I therefore assume that the links between tables is not working correctly. The link between forecast and the joining table appears to be ok, but the joining table to the calendar is not.

    Any pointers would be gratefully received.

    Thanks, James

    #10591

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468

    James,

    Are you working with Power BI or a version of Excel (if Excel, which version: 2010, 2013 or 2016)?

    What you describe is an issue of granularity (lowest level of comparison within a model), and there are different approaches.

    What is your desired level of granularity?

    Taking your data as is, there is only a comparison on the year, unless you choose to allocate down to a lower level of granularity (a valid option). Marco Russo and Alberto Ferrari have given a solution for allocation which you can read about at this link or more generally at this link.

    I am unclear about your use of the joining table, which is joined to “calendar sales”. Is calendar sales the name of your sales table or calendar table or another table in your model or is the joining table linked to calendar, sales, and forecast tables? Please advise.

    Tom

    #10594

    jhd
    Participant
    • Started: 1
    • Replies: 5
    • Total: 6

    Thanks Tom.

    I’m using Excel 2016, and the lowest level of granularity I’m working to is years.

    Having read through the links that you supplied, and trying to understand them, I have concluded that the ‘joining table’ that I had created is not necessary.

    I have created the measure: IsForecastValid

    =NOT (
    ISFILTERED ( ‘Calendar'[Date])
    || ISFILTERED ( ‘Calendar'[Fiscal_Month_Number] )
    || ISFILTERED ( ‘Calendar'[Fiscal_Quarter])
    )

    and that appears to be working correctly.

    I have also created Forecast_Volume

    =if([IsForecastValid],

    CALCULATE(
    SUM(Forecast[MC_5Y_Forecast]),
    FILTER(ALL(Forecast[FYName]),
    CONTAINS(VALUES(‘Calendar'[FYName]),’Calendar'[FYName],Forecast[FYName])
    ),
    FILTER(ALL(Forecast[GOLDEN_ID]),
    CONTAINS(VALUES(Golden_IDs[GOLDEN_ID]),Golden_IDs[GOLDEN_ID],Forecast[GOLDEN_ID])
    ),
    FILTER(ALL(Forecast[PLANT]),
    CONTAINS(VALUES(MASTER_DATA[PLANT]),MASTER_DATA[PLANT],Forecast[PLANT])
    )
    ),
    blank())

    but it is only half working. It is giving the correct results for years in which I have actual sales, it is not giving any values for future years. Do you have any idea why this is?

    Thanks, James

     

    #10596

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468

    James,

    I like your use of CONTAINS.

    If you were Rob Collie, the first thing I would check would be the last date in your calendar table and make sure the calendar table was not limited to only dates that covered the sales table. 🙂 Seriously, though, I would definitely check this.

    So your pivot is filtered by ‘Calendar'[FYName], Master_Data[PLANT], and Golden_IDs[GOLDEN_ID] or an equivalent unique name from the Golden_IDs table corresponding to Golden_ID–any other fields added to pivot drop zones for Rows, Columns, or Filters?

    While testing, are any slicer buttons selected (other than all of them) or are there values set for any pivot Filters that might affect your results?

    If you enter only the [IsForecastValid] measure into the pivot’s Values drop zone, does it populate with TRUE for all future calendar fiscal year names?

    Do any records in the forecast table have non-blank values in the MC_5Y_Forecast, Golden_ID and Plant columns?

    If everything still checks out, could you put up some sample data?

    Tom

    #10597

    jhd
    Participant
    • Started: 1
    • Replies: 5
    • Total: 6

    Hi Tom, my calendar goes up to 31/3/2028 (31st March is the end of our financial year) and when I select the IsForecastValid function the financials years to 2027 appear and all values are TRUE. There are no connected slices, and clearing filters (which I had on the plant) has had no effect.

    There are blanks in the MC_FY_Forecast as some products don’t have a forecast for every year.

    In my calendar and MC_FY_Forecast tables I have “FYName” (FY17, FY18, FY19 etc.) which is the name that I have given to each financial year. In my pivot table, if I swap “FYName” in the Calander table for the one in the MC_FY_Forecast table, then it works (but my objective is to see actual and budget sales all together).

    There is no relationship between FYName in MC_FY_Forecast and FYName in Calendar, as this would be a many to many relationship.

     

     

    #10600

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468

    You mentioned previously that you had created a join table, but did you approach it like this:

    Create a table that you could call something like Fiscal_Years that has one row for each fiscal year.

    Next, create relationships between the new fiscal years table and (1) the calendar table and (2) the forecast table (not to the sales table).

    Something important to check here: make sure the relationship between the fiscal years table and the forecast table shows the forecast table on the many-side of the relationship.

    On a new worksheet, create a pivot table with the fiscal year column from the new fiscal years table on pivot rows and then add simple measures that are just essentially sum of sales amount and SUM(Forecast[MC_5Y_Forecast]).

    You should see a year by year comparison, where forecast amounts continue into the future.

    As of this point, let me know if this is what you see or if you see something different.

    #10622

    jhd
    Participant
    • Started: 1
    • Replies: 5
    • Total: 6

    Hi Tom, I’ve created a Table which has unique values of FYName (FY17, FY18, FY19…) and connected it to the calendar and forecast tables. The calendar and forecast tables are on the ‘many’ side of the relationship.

    I have created a new pivot table:

    FYName (from the new table) in the rows and Sum of MC_FY_Forecast in the columns. Result: MC_5Y_Forecast appears for each year.

    I then added Forecast_Volume measure, and it appeared for FY18 only. I don’t know if this is relevant, but MC_5Y_Forecast does not equal Forecast_Volume. This is because there is a slight mismatch between the Golden_IDs in the Forecast and Sales tables.

    One thing I have just noticed though is that the Grand Total (FY18 + FY19 etc) for MC_5Y_Forecast is correct. The Grand Total for the Forecast_Volume appears to be adding values that are not have not appeared in the rows above (see link). How does this happen?

    https://www.dropbox.com/s/8fjr8legq8pzage/table.PNG?dl=0

     

    Thanks again for your continued help!

    #10628

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468

    Regarding having a grand total when values that “did not appear in the rows above”: this occurs because Power Pivot creates totals by removing filters, not by obtaining values from other cells.

    Something is occurring in the measure for zForecast_Volume2 that is filtering out individual years later than FY18 (maybe a check phrase that begins with “IF”), but does not contain a restriction on the total.

    Can you copy the formula for zForecast_Volume2 into your next post?

    #10629

    jhd
    Participant
    • Started: 1
    • Replies: 5
    • Total: 6

    zForecast_Volume2=

    =if([IsForecastValid],
    CALCULATE(
    SUM(Forecast[MC_5Y_Forecast]),
    FILTER(ALL(Forecast[FYName]),
    CONTAINS(VALUES(‘Calendar'[FYName]),’Calendar'[FYName],Forecast[FYName])
    ),
    FILTER(ALL(Forecast[GOLDEN_ID]),
    CONTAINS(VALUES(Golden_IDs[GOLDEN_ID]),Golden_IDs[GOLDEN_ID],Forecast[GOLDEN_ID])
    ),
    FILTER(ALL(Forecast[PLANT]),
    CONTAINS(VALUES(MASTER_DATA[PLANT]),MASTER_DATA[PLANT],Forecast[PLANT])
    )
    ),
    BLANK()
    )

     

    IsForcastValid=

    NOT (
    ISFILTERED ( ‘Calendar'[Date])
    || ISFILTERED ( ‘Calendar'[Fiscal_Month_Number] )
    || ISFILTERED ( ‘Calendar'[FQName])
    )

    #10638

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468

    At this point, it is hard to say more without working with some sample (sanitized) test data.

    Would I be correct in assuming that the forecast table also has relationship lines between it and the tables for master data and golden ID’s?

    Out of curiosity, with the same pivot, what would be an image with the following measures:

    (1) Without [IsForecastValid]

    zForecast_Volume_Test_1 :=
    CALCULATE (
        SUM ( Forecast[MC_5Y_Forecast] ),
        FILTER (
            ALL ( Forecast[FYName] ),
            CONTAINS ( VALUES ( 'Calendar'[FYName] ), 'Calendar'[FYName], Forecast[FYName] )
        ),
        FILTER (
            ALL ( Forecast[GOLDEN_ID] ),
            CONTAINS (
                VALUES ( Golden_IDs[GOLDEN_ID] ),
                Golden_IDs[GOLDEN_ID], Forecast[GOLDEN_ID]
            )
        ),
        FILTER (
            ALL ( Forecast[PLANT] ),
            CONTAINS ( VALUES ( MASTER_DATA[PLANT] ), MASTER_DATA[PLANT], Forecast[PLANT] )
        )
    )

    (2) Without calendar filters.

    zForecast_Volume_Test_2 :=
    CALCULATE (
        SUM ( Forecast[MC_5Y_Forecast] ),
        FILTER (
            ALL ( Forecast[GOLDEN_ID] ),
            CONTAINS (
                VALUES ( Golden_IDs[GOLDEN_ID] ),
                Golden_IDs[GOLDEN_ID], Forecast[GOLDEN_ID]
            )
        ),
        FILTER (
            ALL ( Forecast[PLANT] ),
            CONTAINS ( VALUES ( MASTER_DATA[PLANT] ), MASTER_DATA[PLANT], Forecast[PLANT] )
        )
    )

    (3) Without Golden ID filters

    zForecast_Volume_Test_3 :=
    CALCULATE (
        SUM ( Forecast[MC_5Y_Forecast] ),
        FILTER (
            ALL ( Forecast[PLANT] ),
            CONTAINS ( VALUES ( MASTER_DATA[PLANT] ), MASTER_DATA[PLANT], Forecast[PLANT] )
        )
    )

    (4) Without Plant filters

    zForecast_Volume_Test_4 :=
    CALCULATE ( SUM ( Forecast[MC_5Y_Forecast] ) )
Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic.