• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Renaming measures in pivot tables

DanSkears

New Member
I have two measures in a pivot table. One is a simple sum and the other is the sum displayed as a percentage of the total. I'm using vba to pareto the results but the vba is struggling to distinguish between the two measures. Recording a macro comes back with the same measure name. Renaming the measures doesn't solve the problem. Any ideas ?
 

Chihiro

Excel Ninja
You can't have identical names for fields. So you must have different name.

Are you checking PivotTable.DataFields?
Code:
Sub Demo()
Dim pvt As PivotTable
Dim pvF As PivotField

Set pvt = ThisWorkbook.Sheets("Sheet1").PivotTables(1)

For Each pvF In pvt.DataFields
    Debug.Print pvF.Name
Next
End Sub
 

DanSkears

New Member
Thanks for the tip.

I have uploaded an image of the pivot table its fields and the pertinent messages from the following vba.

Sub ListAllItemObjects()
For Each pvt In ActiveSheet.PivotTables
For Each fld In pvt.PivotFields
For Each itm In fld.PivotItems
MsgBox itm
Next itm
Next fld
Next pvt
End Sub

So the first column "NCR Count" is a count of the field 'NCR Reference'. The second is the count expressed as a percentage of the grand total. And the third is a sum of the field 'NC Cost in GBP'.

I have renamed the first two fields / columns but not the third.

According to the vba the first two fields / columns have the same measure name 'Count of NCR Reference'. So vba struggles to pareto each column individually. And the renaming hasn't modified the underlying name 'Count of NCR Reference'.

But I have had a brain wave. Whether I pareto by NCR Count or NCR Count As Percentage I can use the one vba routine,

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[tblDefects].[Reason].[Reason]").AutoSort xlDescending, _
"[Measures].[Count Of NCR Reference]", ActiveSheet.PivotTables("PivotTable1"). _
PivotColumnAxis.PivotLines(1), 1

So problem solved ... or at least worked around
 

Attachments

Chihiro

Excel Ninja
Glad you solved it.

FYI - You are checking PivotFields and not DataFields, hence the same name for the field (since it shows underlying field used in DataField, in this case a measure). By using DataFields, you should be able to see what name the field was actually given when used in PivotTable fields.

Kind of confusing, I know, but this is how object model was built by MS ;)
 
Top