VBA to add calculated measure to PowerPivot Datafield

[nussidom]

Hi,

first post here. Been searching the internet for a solution to this question but to no avail.

I'm not experienced in VBA but I need to automate one task so I hacked up some code. Got pretty close but now I don't know how to use previously created calculated measures as datafields with a PowerPivot data model. Recording a macro for dragging calculated measures to 'Values' box doesn't work (macro recording turns out empty). Would really appreciate some help. VBA code snippet below.

' define a new measure and use it as datafield
    Set objCubeField = objPivotTable.CubeFields.GetMeasure( _
        AttributeHierarchy:=objPivotTable.CubeFields( _
            "[" & objListObjectWithData.Name & "]." & _
            "[" & objListObjectWithData.ListColumns(18).Name & "]"), _
        Function:=xlCount, _
        Caption:="Total Clients")
    objPivotTable.AddDataField objCubeField
    objPivotTable.DataFields(1).Caption = "Total Clients"

' add calculated measures
    Set myModel = ActiveWorkbook.Model
    Set myModelTable = myModel.ModelTables.Item("Table1")
    Set myModelMeasures = myModel.ModelMeasures
    myModelMeasures.Add MeasureName:="Total Verified Clients", _
                           AssociatedTable:=myModelTable, _
                           Formula:="COUNTROWS(FILTER(Table1; Table1[Verified] = ""1""))", _
                           FormatInformation:=myModel.ModelFormatGeneral
                           
    myModelMeasures.Add MeasureName:="Verification Rate", _
                           AssociatedTable:=myModelTable, _
                           Formula:="ROUND(((COUNTROWS(FILTER(Table1; Table1[Verified] = ""1""))/COUNTROWS(Table1))*100);2)", _
                           FormatInformation:=myModel.ModelFormatGeneral

'use calculated measures as a datafield
???

Thank you and regards

 

[mart37]

    myPivotTable.AddDataField myPivotTable.CubeFields("[Measures].[Verification Rate]")
    myPivotTable.AddDataField myPivotTable.CubeFields("[Measures].[Total Verified Clients]")