Calculating data in pivots

[Rip1971]

I am trying to calculate data in a pivottable but cannot get the right %compliance.
calculation should be something like Due/ (Due+overdue) but how do i count the due and the total from my data source or in my pivot based on itemid.

Who can help me with this problem.

[Mrrrr]

Wouldn't it be possible to use 1 and 0 instead of Y and N for Overdue?
Cause then you could sum the 1 to find the number of Overdues, then the difference between total count and that sum of 1s would be the Not-Overdue.

As far as I know count / countif formulas don't work in Pivot.
Don't know about PowerPivot (never used).

[Rip1971]

the report is generated from website/ database. i guess we can rename the Y and N into numbers. that then needs to ben done somewhere before pivot is made.

[Mrrrr]

It should be possible after the pivot is made, then just refresh the pivot.

Or, assuming the generated data always has the same number of columns and the same column order, have another column at the end of the generated data with a formula like:

Please Login or Register  to view this content.

(I noticed there is a Table as Pivot Source).

Of course, that column also has to be included in the pivot by default.
And since you have a Table under the Pivot the column will be automatically included.

[Rip1971]

It should be possible after the pivot is made, then just refresh the pivot.

Or, assuming the generated data always has the same number of columns and the same column order, have another column at the end of the generated data with a formula like:

Please Login or Register  to view this content.

(I noticed there is a Table as Pivot Source).

The report does have the same amount of columns but will need to add one for the new format but does not have same amount of rows. How can i make it so that it automatically adds the formula to the a new column and changes the N/Y to 0/1 form.

and then the original question remains how can i calculate the compliance from this data.

[JeteMc]

Perhaps the following will be helpful.
1. Add a new column (S) to the source data as modeled on Sheet1 using: =COUNTIFS([Overdue],"N",[Item Id],[@[Item Id]])/SUM(COUNTIFS([Overdue],"N",[Item Id],[@[Item Id]]),COUNTIFS([Overdue],"Y",[Item Id],[@[Item Id]]))
2. Place the new Compliance column in the Values area of the field list and choose either Average, Max or Min for the summarizing the data.
A small sample of the resulting pivot table is modeled on the PT sheet.
Let us know if you have any questions.

[Rip1971]

Thank you for replying.
i think a did not make my intentions clear before.
let me try to do it better. i have added a new file which has a Data sheet which is created from a database and i have no control over te input. (only format). from this data i would like to get an overview of the compliance of each item id.

i thought i could do this by putting this in a pivot table as shown in sheet Pivotpercentage. however i do not know how i can calculate the compliance and show it in an overview like on this pivotsheet.

[JeteMc]

This proposal employs Power Pivot.
First the range on the Data sheet is converted to a table. (Ctrl + t)
Note that you will likely have to change $A$1:$AZ$2030 to $A$2:$AZ$2030 before selecting OK.
From the Power Pivot tab choose Add to Data Model
Use the following calculated measure: Compliance:=CALCULATE(COUNTA(Table2[Overdue]),Table2[Overdue]="N")/COUNTA(Table2[Overdue])
Format the measure as Percentage
Use the Compliance measure in the Values field of the pivot table as modeled on the Power PivotPercentage sheet.
Let us know if you have any questions.

[tmohantyofficial]

Hello guys, I am new to pivot table. I use ms office 2019. Kindly help me with the attached excel. I will be deeply grateful and also would be able to save my job.

regards
Tapan

[JeteMc]

Hello tmohantyofficial and Welcome to Excel Forum.
Please do not break rule #4 by posting a request for help in a thread started by another member, as you have done twice, start your own new thread instead.
Let us know if you have any questions.