Distinct function not correctly counting, summing, or averaging unique values of a field

[Jason Elkin]

I have a calculated column with a set of unique numbers. These values are the result of a sum of time data associated with a property and phase. Periodically the distinct count, sum, or average function will not be correct. I've identified a couple of date ranges where its leaving out a single property causing the error numerator and denominator to be wrong in the average calculation. I'll change the date range and it then pulls in the missing values and provides correct caluclations. The values of the time data being added are all unique and no filters are being used.I've also taken the numerical values out to 9 decimal places.

Count(DISTINCT [sum([Time]]) OVER ([propertyname]], [phasecode]])])

The count is correct if I use the below so it has to be something with the numerical values. However, none of the values are unique.

Count (DISTINCT [propertyname])

Just curious if anyone has ever run into this issue. Thank you

[David Boot-Olazabal]

Hi Jason,

 

Could you provide for an example dxp file to have a better look at how the dataset looks like

 

kind regards,

 

David