Avatar of Sanjay Gandhi
Sanjay GandhiFlag for India

asked on 

Stop Pivot Table from changing Top 3 report even after changing the field

Hi,

I have a pivot table displaying top 3 values on for example, Customer field.

When I change from Customer field to Supplier field, I have to again set Top 3.

Can there be no method that my Pivot table filters keep showing Top 3 always even when I change from say Customer to Supplier.

Warm regards,

Sanjay.
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Sanjay Gandhi
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Hello Sanjay
Can you please post a sample workbook ?
Gowflow
Avatar of Sanjay Gandhi
Sanjay Gandhi
Flag of India image

ASKER

Sure,

In the given workbook, there is a sheet containing Data, and there is a sheet containing 2 Pivots.
In Pivot sheet, there is a Pivot for Top 3 Dealers, and another for Top 3 Customers.

If I don't want 2nd Pivot, and I make a change in first Pivot, field from Dealer to Customer, then entire list for Customers displays. What I want is, when I change the field from Dealers to Customers, it should maintain Top 3 condition, and display Top 3 Customers when I replace Dealers with Customer field. I should not be making another Pivot for Customers.

Regards,
Sanjay.
Top 3 Values - Pivot.xlsx
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Looks like you have to do it once for both cases. Once done you can change one to the other.
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Well we can if you are interested via VBA.
Gowflow
Avatar of Sanjay Gandhi
Sanjay Gandhi
Flag of India image

ASKER

Sure,
That will be a good help. I know some VBA, so I should be able to crack the code you send. Can you help?
Sanjay.
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Well here is the code that I embeded in a module in the attached workbook. I also created 2 options buttons that you can toggle between Dealer and Customer to make your choice. To add options I created a combo dropdown where you can select what top you want Top 10 Top 5 … Top 3 is the default.

Here is the code

Option Explicit

Sub Top3(sSource As String)
On Error Resume Next

Dim WS As Worksheet
Dim Pvt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim Pfilter As PivotFilter

'MsgBox sSource
Application.ScreenUpdating = False

Set WS = ActiveSheet

For Each Pvt In WS.PivotTables
    If Pvt.Name = "PivotTable3" Then
        
        '---> Clear All Filters and fields and Data
        Pvt.ClearAllFilters
        
        For Each pf In Pvt.PivotFields
            For Each df In Pvt.DataFields
                df.Orientation = xlHidden
            Next df
            If Not pf Is Nothing Then pf.Orientation = xlHidden
        Next pf
        
        '---> Create Field as selected
        Pvt.AddFields RowFields:=sSource
        Set pf = Pvt.PivotFields(sSource)
        pf.LabelRange = sSource
        
        Pvt.AddDataField Pvt.PivotFields("Quantity")
        Set df = Pvt.DataFields(1)
        df.LabelRange = "Total Quantity"
        df.LabelRange.HorizontalAlignment = xlRight
        df.NumberFormat = "#,##0 "
        
        '---> Filter Top N
        'pvt.PivotFields(sSource).PivotFilters.Add Type:=xlTopCount, DataField:=df, Value1:=3, Order:=xlDescending
        pf.PivotFilters.Add Type:=xlTopCount, DataField:=df, Value1:=Val(Sheets("Pivot").ComboBox1.Value), Order:=xlDescending
        pf.AutoSort Order:=xlDescending, field:=df
        
        Pvt.RowGrand = True
    End If

Next Pvt


Application.ScreenUpdating = True
End Sub

Open in new window



Enjoy.

Gowflow
Top-3-Values---Pivot-V01.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Sanjay Gandhi
Sanjay Gandhi
Flag of India image

ASKER

Wonderful!! It works like a charm.
The two option buttons, finally gave it a lift. Thanks, much needed solution.
(Sorry I got little too busy with support online, could not revert earlier).

Thanks a ton!!
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Your welcome glad I could help. Please feel free to ask any other question you may need help with and case you want my participation please put a link in this question as I will keep monitoring it. I am thinking maybe you need to adapt this to your real data or any other issue.
Gowflow
Avatar of Sanjay Gandhi
Sanjay Gandhi
Flag of India image

ASKER

Sure. How to put that link you are saying...
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

well when you create a new question you simply copy the address Http of that question and paste it in here. I will take it from there. You can put in the question description that this is a follow-up on previous question etc ...
Gowflow
Avatar of Sanjay Gandhi
Sanjay Gandhi
Flag of India image

ASKER

Thanks. 😊
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo