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