Hi All,
I'm using Excel 2013. My Raw data has a column for tasks, a columns for branch and a column for status. There are 3 options for status (completed, Pending, Cancelled). I used a pivot table to count the number of cancelled, completed and pending tasks in every single branch. (I put status in the value area in the pivot table fields settings). I ended up having a pivot table with Branches names in rows and status in columns where i have the count of cancelled, pending and completed tasks. Now I'm trying to develop a chart for the completion rate in every single branch so I'm trying to add a calculated item and use this formula Completed/(Total-Cancelled) but i'm getting this error message ""If one or more fields in the PivotTable have calculated items, no fields can be used in the data area two or more times, or in the data area and another area at the same time. If you are trying to add a field, remove the calculated items and add the field again. If you are trying to add a calculated item, change the PivotTable report so that no field is used more than once and then add the calculated item."
As an alternative I tried to create a normal table and connect it to the pivot table using Getpivot however I got #REF error in some cells and things get messy as some branches disappear from the pivot table and when I use slicers and GetPivot can't deal with it. Is there anyway to fix the problem?
Thanks gor your help!
I'm using Excel 2013. My Raw data has a column for tasks, a columns for branch and a column for status. There are 3 options for status (completed, Pending, Cancelled). I used a pivot table to count the number of cancelled, completed and pending tasks in every single branch. (I put status in the value area in the pivot table fields settings). I ended up having a pivot table with Branches names in rows and status in columns where i have the count of cancelled, pending and completed tasks. Now I'm trying to develop a chart for the completion rate in every single branch so I'm trying to add a calculated item and use this formula Completed/(Total-Cancelled) but i'm getting this error message ""If one or more fields in the PivotTable have calculated items, no fields can be used in the data area two or more times, or in the data area and another area at the same time. If you are trying to add a field, remove the calculated items and add the field again. If you are trying to add a calculated item, change the PivotTable report so that no field is used more than once and then add the calculated item."
As an alternative I tried to create a normal table and connect it to the pivot table using Getpivot however I got #REF error in some cells and things get messy as some branches disappear from the pivot table and when I use slicers and GetPivot can't deal with it. Is there anyway to fix the problem?
Thanks gor your help!
