Hidden Data cleanliness issue led to basic average in Spotfire not giving the same answer as in Excel
I am calculating a simple average in Spotfire across a column that is a REAL column in Spotfire which comes from a % column in excel .
I have values across the 12 months of the year for multiple groups. The Spotfire report will be run for a user selected month out of the year for all the groups.
When I compare the results of the Avg() function in Spotfire to the average for the Average() in Excel for a representative set of data, January, I do not get the same results in Excel and Spotfire.
However when I export the data from Spotfire and bring it into excel, the excel Average() of the exported data now shows the Excel results not the Spotfire result.
I am confused Why am I getting different results in excel and spotfire? I need to understand because my stakeholder will "validate" the results of the Spotfire reports by using excel. They will be very confused when the results are not the same!
Excel file showing raw data and exported Spotfire data side by side with Average() calculated.
Screenshot of Spotfire Avg() function defined for report.
Screenshot of Spotfire Avg() results in report.