Splitting a pivot table into two

**JRDavie** · 07-19-2021, 07:45 AM

Hi, I need to produce two pivot tables, one showing the top 30 items and the other showing the next 30. Creating a calculated field (rank value) doesn’t seem to work as it ranks within all columns in the table (I.e. produces multiple 1sts, etc). Using value filters allows the top n (first table works) but I can’t uses these for 31-60. Sounds like this should be easy but I have just wasted a whole morning trying to figure it out. Any advice? Willing to use DAX if there is a way to achieve this via the data model. Thanks!

**tanasedn** · 07-19-2021, 08:05 AM

Can you, please, attach a sample file with no confidential information?

**JRDavie** · 07-19-2021, 08:33 AM

Thanks for looking at this, please see attached example.

**JRDavie** · 07-19-2021, 08:42 AM

A slightly better example.

**tanasedn** · 07-19-2021, 09:29 AM

Add your table to the PowerPivot data model.
Add a new column using

Formula:

Please Login or Register  to view this content.

From data model insert a pivot and choose this new column as a first row label then continue with ID and so on
Sort by this column ascending and filter as you wish.

**JRDavie** · 07-19-2021, 09:50 AM

Apologies, I should have said that my pivot includes a filter which prevents RANKX from working. Please see updated file.

**JeteMc** · 07-23-2021, 10:36 AM

Perhaps the following will help.
As modeled on Sheet1, a new column (Rank by Division) is added to the source data.
The new column is populated using: =IF([Division]<>J$1,"",SUMPRODUCT(([Value]>[@Value])*([Division]=J$1))+1)
The pivot table on the left displays the top 30 values a value filter, as used originally, and sorting the values field in descending order.
The pivot table on the left displays the next 30 values using a 'between' values filter of the Rank by Division field and also sorting that field in ascending order.
When the filters are changed the left pivot automatically updates, however the one on the right must be refreshed.
Let us know if you have any questions.

Splitting a pivot table into two

LinkBack

Thread Tools

Rate This Thread

Display

Splitting a pivot table into two

Re: Splitting a pivot table into two

Re: Splitting a pivot table into two

Re: Splitting a pivot table into two

Re: Splitting a pivot table into two

Re: Splitting a pivot table into two

Re: Splitting a pivot table into two

Thread Information

Users Browsing this Thread

Similar Threads

Pivot Table Help - Splitting Data Entry Points

Table to column problem to create dataset for pivot table [Bloomberg data to pivot table]

pivot table splitting different colums with same value

Stop row from splitting on 2 pages when printing pivot table

Splitting up pivot table fields

Excel 2007 : Splitting Checkbox/Choice Data in Pivot Table

Pivot Table - Splitting out rows

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions