QuickSight中AvgOver函数抛出VISUAL_CALC_REFERENCE_MISSING错误的技术求助

DevOps 小满

2026-4-30

Fixing VISUAL_CALC_REFERENCE_MISSING Error in QuickSight Calculated Field

Hey there! Let's work through this QuickSight issue you're facing. I'll break down why the error is happening and walk you through the fixes step by step.

What's Causing the `VISUAL_CALC_REFERENCE_MISSING` Error?

Your original calculated field uses a nested aggregation (sum(Score) inside avgOver()) that doesn't align with your actual goal, and likely conflicts with how QuickSight expects dimensions to be set up in your visualization. This error usually pops up when the calculated field's logic doesn't match the dimensions/measures you've added to your visual, or when the aggregation hierarchy is misaligned.

Step 1: Correct the Calculated Field Logic

Your goal is to get the average Score for each Year + MeasureID combination. You don't need to sum the scores first—instead, calculate the average directly over the two dimensions. Use this formula instead:

avgOver(Score, [MeasureID, Year], PRE_AGG)

The PRE_AGG parameter ensures QuickSight calculates the average at the row level before aggregating by your specified dimensions, which gives you the exact average you need for each group.

Alternatively, you can skip the calculated field entirely: just drag the raw Score field to your visual's "Values" area, then change its aggregation to Average, and add Year and MeasureID to the "Dimensions" area. QuickSight will handle the grouping automatically.

Step 2: Verify Your Visualization Setup

Make sure your visual (like a table or bar chart) has these elements configured:

Dimensions: Add both Year and MeasureID here—this tells QuickSight to group your data by these two fields.
Values: Add either your corrected calculated field, or the Score field set to Average aggregation.