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

New Contributor

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.

3 Replies

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

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])


Glad I could help

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