Error in calculated field Pivot table

[littlevillage]

Hi all,

I am trying to calculate % of values in Column B/Column C

I have used Calculated Field, but it return error #DIV/0! in Column D

My expect result are values as in Column E

My Office version: 2021

I have added an image and the link of the sample file as below

Thank you

https://drive.google.com/file/d/1SHLQP5EqB_iGz12QIElY3-BidIU8WBtv/view?usp=sharing 

[#2]

@littlevillage You can't do that in a regular pivot table. You need to use Power Pivot (i.e. Data Model) and create a some measures that count both columns and on that calculates the percentage. See attached file in Sheet1.

[#3]

@Riny_van_Eekelen 

Thanks for your response

The sample file work correctly, but i have some issue with my full file .

In the Pivottable 1 , I will count number of Tracking number with criterias "Got' in column Pickup and "late" in column Deadline 

In the Pivottable 2 , I will count number of Tracking number with criteria "Got" in colum Pickup

The expect result: the values % in column I

How can I write a formula in Power Pivot for this case

I have added an image and the link of FullFile as below

https://drive.google.com/file/d/1wNejuX0qbiCPs3fYv2gzxzFjvx7eAuAd/view?usp=sharing 

[#4 · Accepted solution]

@littlevillage The file you shared caused some hick-ups on my side. Removed the query and the large table that was loaded to the DM. Also removed the VBM modules and save the file as a regular xlsx workbook.

Pleas see if the (power pivot), starting in N4, is what you had in mind. Note also that your Expected result column can not be correct as the tracking numbers in both G and J don't line up.

[#5]

@Riny_van_Eekelen 

This is the first time, I know about Power Pivot
Thank you for sending me to the Power Pivot that's the power of excel