Hi,
May i know how to sort pivot tables by top 5 values with a commandbutton(As attachment)? I have tried using VBA but keep having error -" Application-defined or object-defined error" with the below codes.
Sub Filter_Sort2()
ActiveWorkbook.Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("SR Account Name").AutoShow Type:=xlAutomatic, Range:=xlTop, Count:=5, Field:="Sum of Unique of Activity Number"
End Sub
Pivot field's row label
1)SR Account Name
2)SR Country
3)SR Asset Serial Number
4)Platform
Value field
5)Sum of Unique of Activity Number
Can it sort with power query or any other advanced method instead of vba? I need to sort the top 5 values without group by the alphabetically order(SR Account Name) because pivot columns auto group by same company name.
I do not know what method to sort it. Any kind person to help me?Thanks!
Best Regards,
Hooi Thin
Hi Hooi Thin
As a workaround, you can try moving the SR Asset Serial Number field in your PivotTable row labels to the top. Click the Filter & Sort button to sort and show top 5. Refer attachment.
Can't help you with Power Query as I am not familiar with it.
Hope this helps.
Sunny
Hi Sunny,
Thanks for your solution, it works. 🙂
But what i want to display out on my project is stay back the same SR Account Name at first column. Customer wanted to see Customer Name at priority instead of moving the SR Asset Serial Number field in the PivotTable. Any other way Sunny?
Best Regards,
Hooi Thin
Hi Hooi Thin
The best that I can think of is to create a helper column in your source data that will allow a unique grouping without affecting the SR Account Name. You can hide the helper column. I can't think of another way using a PivotTable because of the auto grouping.
The macro will do the necessary using the helper column.
Hope this helps.
Sunny
Hi Sunny,
Quite a good idea also, Thank you very much! I am working with it now. 🙂
Best Regards,
Hooi Thin
Hi Sunny,
Can you help me for another issue related to the helper column that you teached me last time.
I wanted to sort the top 5 values based on helper column and then hide out the display of helper column.
I tried not to hide the whole column but just want to hide the particular field in the pivot table but still fail with visible property.
Can you check for me whats wrong with my code?Thanks!
Regards,
Hooi Thin
Hi Hooi Thin
I am not aware of any method that you can hide a field with affecting the PivotTable.
Maybe you can just hide the entire column instead.
Sunny
