Hi,
I have a set of numeric values in 2 decimal place.
When I use the measure aggregation set as average in Quicksight, it returns an average value. However, when I export this table into Excel, and perform an average in Excel, there is discrepancy between the calculated values in Quicksight and Excel.
For example, I observed Quicksight calculated 1113.45 and my Excel is 1105.53.
It makes no sense as the exported Excel from Quicksight is literally the same as the one I saw in the visualisation.
How does Quicksight calculates average?
Note: My dataset is already a calculated value, Quicksight simply take these values to perform the average.
Additional info: I am trying to calculate the average energy across different buildings. If I select only one building, the average is correct. However if I select more than one building, there will be averaging discrepancy.
Hi @dixerio
I checked with the sample data and found that if the measures have identical values across dimensions, the Table visual type does not show the count of repeating values, whereas the Pivot table does. This could be the reason for the discrepancies. Please try using the Pivot visual type and verify if any records are excluded when exporting the results to Excel.
Example: Please refer to the following image for reference. Building A has 10 values, consisting of 8 unique values and 2 same values. In the Table visual type, when the measure field is added to the ROW section, its showing the repeated values once. However, when the same measure is added to the VALUE section, the average is aggregated correctly.
If you still need any further help. Please recreate this issue in Arena using sample non-sensitive data, I can take a deeper look to see what is going on.
