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.
Thank you and regards
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.
VBA Code:
' 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