Home

Calculated Field for Pivot Table - Unable to Calculate

%3CLINGO-SUB%20id%3D%22lingo-sub-784278%22%20slang%3D%22en-US%22%3ECalculated%20Field%20for%20Pivot%20Table%20-%20Unable%20to%20Calculate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784278%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20values%20shown%20in%20my%20pivot%20table%20per%20department%2C%20%3CSTRONG%3ECount%20of%20People%3C%2FSTRONG%3Eand%20%3CSTRONG%3ESum%20of%20People%20Using%20Widget%3C%2FSTRONG%3Es.%20I%20want%20to%20divide%20the%20Sum%20of%20People%20Using%20Widgets%20by%20the%20Count%20of%20People%20to%20get%20a%20percentage%20of%20the%20people%20using%20widgets.%20Whenever%20I%20try%20to%20insert%20a%20Calculated%20Field%2C%20I%20get%20a%20result%20of%20%23DIV%2F0!.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125310iB67F532104896EFF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-784278%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-784725%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20Field%20for%20Pivot%20Table%20-%20Unable%20to%20Calculate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784725%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385845%22%20target%3D%22_blank%22%3E%40CarlW%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ehow%20do%20you%20create%20the%20calculated%20field%3F%20In%20this%20screenshot%2C%20I%20divide%20the%20columns%20by%20one%20another.%20The%20%23Div%2F0%20error%20only%20shows%20if%20the%20people%20column%20is%20zero.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20693px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125334iE8F2CE22A1B10124%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-08-02_08-38-07.jpg%22%20title%3D%222019-08-02_08-38-07.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-784764%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20Field%20for%20Pivot%20Table%20-%20Unable%20to%20Calculate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784764%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20quick%20response%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20created%20the%20formula%20in%20the%20same%20way.%20Because%20the%20People%20field%20is%20a%20count%20of%20how%20many%20people%20there%20are%2C%20I%20am%20wondering%20if%20it%20can't%20calculate%20this%20because%20the%20basis%20for%20the%20counts%20is%20people's%20names.%20It%20can't%20do%20calculations%20based%20on%20the%20source%20data.%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%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125336i3F23A834EE1B8440%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-784795%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20Field%20for%20Pivot%20Table%20-%20Unable%20to%20Calculate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784795%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385845%22%20target%3D%22_blank%22%3E%40CarlW%3C%2FA%3E%26nbsp%3B%20can%20you%20post%20a%20small%20data%20sample%3F%20You%20can%20attach%20a%20workbook.%20Just%20make%20sure%20to%20replace%20confidential%20data%20with%20fake%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E
CarlW
New Contributor

I have two values shown in my pivot table per department, Count of People and Sum of People Using Widgets. I want to divide the Sum of People Using Widgets by the Count of People to get a percentage of the people using widgets. Whenever I try to insert a Calculated Field, I get a result of #DIV/0!.

clipboard_image_0.png

3 Replies

Hello @CarlW , 

 

how do you create the calculated field? In this screenshot, I divide the columns by one another. The #Div/0 error only shows if the people column is zero.

 

2019-08-02_08-38-07.jpg

Thanks for the quick response @Ingeborg Hawighorst 

 

I created the formula in the same way. Because the People field is a count of how many people there are, I am wondering if it can't calculate this because the basis for the counts is people's names. It can't do calculations based on the source data.

 

clipboard_image_0.png

@CarlW  can you post a small data sample? You can attach a workbook. Just make sure to replace confidential data with fake data.

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies