Pivots and calculated field problem

[Crazybrit]

Hi All,
So I have some data that I cannot edit. I have a column "planned date and time" and a column "actual date and time" my problem is I want to count the number of occurrences where the actual date and time is over 15mins before the planned date and time. This needs to be done outside the original data sheet as I can upload the template spreadsheet to the software i'm using to recreate the calculation.
I have tried creating a pivot using a different column with a unique value for each row and a calculated field with formula.....
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(('Planned Start Date And Time'-'Actual Start Date And Time')>0.0104166666666667,1,0)

but the grand total is returning 1 because the grand total is calculating the whole sheet and is clearly greater than

0.0104166666666667 does anyone know a work around where I don't edit the original sheet of data? thankyou in advance
crazybrit
​

[/FONT]

 

[Misca]

Welcome to the msg board!

Two solutions come to mind. The first one would be if you used Power Query to calculate the additional column and created the pivot table based on that connection. I believe this would be the easiest way and comes with quite a few additional benefits ( you can modify the data structure, use external data sources etc.).

The other way would be to load the table to Power Pivot data model. This could be an addition to the first one ( where you used Power Query to do the calculation ) or you could calculate the number of rows dynamically using DAX. However this might be a bit more difficult if you're not familiar with DAX.