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

%3CLINGO-SUB%20id%3D%22lingo-sub-3205861%22%20slang%3D%22en-US%22%3ECreate%20a%20calculated%20field%20in%20pivot%20table%20based%20on%20values%20in%20multiple%20columns%20of%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3205861%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20with%20the%20following%20columns%2C%20Store%20%23%2C%20State%2C%20Serial%20%23%2C%20Manager%2C%20Date%20Serviced%2C%20Date%20Reported.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sysisoft_1-1645640833499.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F350765i02FC9091EA5C2562%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Sysisoft_1-1645640833499.png%22%20alt%3D%22Sysisoft_1-1645640833499.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20pivot%20table%20that%20has%20rows%20of%20Manager%20and%20State%2C%20and%20columns%20of%20Distinct%20Count%20of%20Store%20%23%2C%20Count%20of%20Serial%20%23%2C%20Count%20of%20Date%20Serviced.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sysisoft_0-1645640635606.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F350764i404F673FC1685D58%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Sysisoft_0-1645640635606.png%22%20alt%3D%22Sysisoft_0-1645640635606.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20calculate%20%22Count%20of%20Date%20Serviced%22%2FCount%20of%20Serial%20%23%22%20(%25%20Completed).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20last%20column%20is%20not%20integrated%20into%20the%20pivot%20table.%26nbsp%3B%20My%20task%20is%20to%20make%20it%20a%20calculated%20field%20in%20the%20Pivot%20Table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20navigate%20to%20PivotTable%20Analyze-%26gt%3BCalculations%20-%26gt%3B%20Fields%2C%20Items%20and%20Sets%20-%26gt%3B%20Calculated%20Field%20is%20grayed%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EData%20in%20pivot%20table%20above%20is%20not%20representative%20of%20the%20data%20table%20displayed%20above.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3205861%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3206650%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20calculated%20field%20in%20pivot%20table%20based%20on%20values%20in%20multiple%20columns%20of%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3206650%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1282160%22%20target%3D%22_blank%22%3E%40Sysisoft%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20create%20a%20Distinct%20Count%20you%20had%20to%20load%20your%20table%20to%20the%20Data%20Model%20(Power%20Pivot)%2C%20reason%20why%20options%26nbsp%3B%3CSPAN%3ECalculated%20Field%2C%20Calculated%20Item...%3C%2FSPAN%3E%26nbsp%3Bare%20grayed%20out%3C%2FP%3E%3CP%3EYou%20have%20to%20create%20a%20DAX%20measure%2C%20something%20like%20(assuming%20your%20Pivot%20source%20table%20is%20named%20Table1)%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%25%20Completed%20%3A%3D%0ADIVIDE%20(%20Table1%5BCount%20of%20Date%20Serviced%5D%2C%20Table1%5BCount%20of%20Serial%5D%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDAX%20Reference%20is%20available%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fdax%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehere%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3212096%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20calculated%20field%20in%20pivot%20table%20based%20on%20values%20in%20multiple%20columns%20of%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3212096%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20tip%20on%20Distinct%20Count%20and%20Data%20Model.%20I%20wound%20up%20just%20adding%20a%20%22Measure%22%20in%20the%20Power%20Pivot%20using%20%3D(%5BCount%20of%20Date%20Serviced%5D%2F%5BCount%20of%20Serial%20Number%5D)%3C%2FLINGO-BODY%3E
New Contributor

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

 

Sysisoft_1-1645640833499.png

 

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.

 

 

Sysisoft_0-1645640635606.png

 

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

@Sysisoft 

Glad I could help

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