+ Reply to Thread
Results 1 to 6 of 6

Power Query drawing through Calendar Data OK but Pivot Table shows incorrect values

  1. #1
    Registered User
    Join Date
    09-13-2011
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    6

    Post Power Query drawing through Calendar Data OK but Pivot Table shows incorrect values

    I've created a data model using 2 tables - tbl_calendar and tbl_Delivered_Revenue
    The model is ANALYSIS.xlsx

    I've created bespoke date columns based on the date range in tbl_calendar to form meaningful headers - Trading Year / Trading QTR / Trading Month as gregorian calendars dates are not the financial trading periods.

    This seems to work OK and i've input a relationship between Date (ACTUAL) from tbl_calendar to Start Date from tbl_Delivered_Revenue.

    I've created a simple pivot table using the data model and have Year / QTR Name / Month Name in the Rows which display fine - the issue is when i drop the Value field into [Values] it doesn't list the actual trading month revenue values, just gives me a sum total of ALL of the Value not by tradiong month.

    SAMPLE_IMG.png

    SAMPLE2_IMG.png

    Let me know if you can see where i've gone wrong with this model.

    Thanks
    Attached Files Attached Files
    Last edited by Showpiece11; 04-05-2022 at 11:49 AM. Reason: Sample data attached

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Power Query drawing through Calendar Data OK but Pivot Table shows incorrect values

    First, I suggest you update your profile to reflect the version you are actually using now.

    Second, you haven't shown the relationships diagram, so it's hard to comment on but the errors would certainly suggest that is where the problem is.
    Rory

  3. #3
    Registered User
    Join Date
    09-13-2011
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Power Query drawing through Calendar Data OK but Pivot Table shows incorrect values

    Hi rorya,

    I'm using Excel in office 365 - I've attached sample data and an image of the relationships.

    Thank you for your assistance.
    Last edited by Showpiece11; 04-05-2022 at 03:24 AM.

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Power Query drawing through Calendar Data OK but Pivot Table shows incorrect values

    Hi,

    Remove the nulls in tbl_Calendar, which are currently causing Excel to set up the relationship between the two tables as Many-to-One from tbl_Calendar to tbl_Delivered_Revenue, and since filtering can only propagate from the one to the many side, currently your desired filtering will not take place. After removing the nulls in Power Query, recreate your relationship, selecting tbl_Delivered_Revenue for the first table.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Registered User
    Join Date
    09-13-2011
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Power Query drawing through Calendar Data OK but Pivot Table shows incorrect values

    Thank you XOR LX - solved the problem - much appreciated

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Power Query drawing through Calendar Data OK but Pivot Table shows incorrect values

    You're welcome!

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sorting Pivot Table Pulling from Power Query Data Table
    By perkyparker in forum Office 365
    Replies: 1
    Last Post: 05-22-2021, 03:02 PM
  2. Replies: 2
    Last Post: 02-13-2019, 07:39 AM
  3. POWER Query and Power Pivot Table !!
    By haitham.shop in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-06-2018, 04:09 PM
  4. Power Query and Power Pivot Table
    By haitham.shop in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-06-2018, 06:17 AM
  5. Power Query and Power Pivot Table !!
    By haitham.shop in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-06-2018, 06:15 AM
  6. POWER Query and Power Pivot Table !!
    By haitham.shop in forum Access Tables & Databases
    Replies: 0
    Last Post: 08-06-2018, 05:59 AM
  7. Data Table shows one incorrect value
    By willyt19712 in forum Excel General
    Replies: 8
    Last Post: 06-12-2018, 10:07 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1