Create a calculated field in pivot table based on values in multiple columns of table

[Sysisoft]

I have a table with the following columns, Store #, State, Serial #, Manager, Date Serviced, Date Reported.

I have created a pivot table that has rows of Manager and State, and columns of Distinct Count of Store #, Count of Serial #, Count of Date Serviced.

I am trying to calculate "Count of Date Serviced"/Count of Serial #" (% Completed).

The last column is not integrated into the pivot table.  My task is to make it a calculated field in the Pivot Table.

When I navigate to PivotTable Analyze->Calculations -> Fields, Items and Sets -> Calculated Field is grayed out.

Data in pivot table above is not representative of the data table displayed above.

[#2]

Hi @Sysisoft 

To create a Distinct Count you had to load your table to the Data Model (Power Pivot), reason why options Calculated Field, Calculated Item... are grayed out

You have to create a DAX measure, something like (assuming your Pivot source table is named Table1):

% Completed :=
DIVIDE ( Table1[Count of Date Serviced], Table1[Count of Serial] )

DAX Reference is available here

[#3]

Thanks for the tip on Distinct Count and Data Model. I wound up just adding a "Measure" in the Power Pivot using =([Count of Date Serviced]/[Count of Serial Number])

[#4]

@Sysisoft 

Glad I could help

Re. =([Count of Date Serviced]/[Count of Serial Number]) take a look at DIVIDE function vs. divide operator (/)