+ Reply to Thread
Results 1 to 2 of 2

Sorting Pivot Table Pulling from Power Query Data Table

  1. #1
    Registered User
    Join Date
    05-18-2021
    Location
    Kansas City, MO
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1

    Sorting Pivot Table Pulling from Power Query Data Table

    I am having an issue getting my data to sort twice essentially on a pivot table. I have 2.7MM rows of data fed into power query that then load into the data model to avoid missing out on any data. That then feeds into a number of pivot tables that clean up the data and feed into pie and graph charts. I've attached a sample of what the cleaned data in the pivot tables looks like. The Retailer Dollars sort highest to lowest, but then I need the Competition dollars to sort highest to lowest AFTER the retailer company list is exhausted. next to the pivot table is how I'm wanting the end result to look. Any ideas? I've tried sorting the power query, but because it's feeding into the data model, the sort isn't holding.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,531

    Re: Sorting Pivot Table Pulling from Power Query Data Table

    Hello perkyparker and Welcome to Excel Forum.
    This proposal adds two helper columns to the original data which is then loaded into the data model.
    The first added column (Retail Rank) is populated using: =IF(B2>0,RANK.EQ(B2,B$2:B$11,0),"")
    The second helper column (Sales Rank) is populated using: =IF(D2="",SUM(SUMPRODUCT((C$2:C$11>C2)*(D$2:D$11="")),1,MAX(D$2:D$11)),D2)
    As you stated, sorting the data model by the Sales Rank column does not carry over to the pivot table, however the pivot table itself may be sorted by the Sum of Sales Rank column and then that column (J) could be hidden (for aesthetic purposes).
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Power Pivot/Query Convert Existing Table to Query
    By trisoldee in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-23-2019, 04:27 PM
  2. Replies: 1
    Last Post: 11-12-2018, 02:46 PM
  3. Add custom fields to a table to simulate a pivot table - Power Query
    By nathalielesperance in forum Excel General
    Replies: 2
    Last Post: 10-25-2018, 06:44 AM
  4. POWER Query and Power Pivot Table !!
    By haitham.shop in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-06-2018, 04:09 PM
  5. Power Query and Power Pivot Table
    By haitham.shop in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-06-2018, 06:17 AM
  6. Power Query and Power Pivot Table !!
    By haitham.shop in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-06-2018, 06:15 AM
  7. POWER Query and Power Pivot Table !!
    By haitham.shop in forum Access Tables & Databases
    Replies: 0
    Last Post: 08-06-2018, 05:59 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