Feb 23 2022 10:43 AM
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.
Feb 23 2022 01:23 PM
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
Feb 24 2022 03:25 PM
Feb 24 2022 09:33 PM
Glad I could help
Re. =([Count of Date Serviced]/[Count of Serial Number]) take a look at DIVIDE function vs. divide operator (/)