Forum Discussion
Create a calculated field in pivot table based on values in multiple columns of table
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 https://docs.microsoft.com/en-us/dax/
- 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])
