Forum Discussion
Error in calculated field Pivot table
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
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.
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.
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
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.
