QuickSight · SQL · Reporting grain

Correcting an Overstated KPI

A QuickSight target KPI was 58% higher than expected. I traced the issue back to the SQL feeding the dashboard and found that the full monthly account target was being repeated across employee rows under the same account. When QuickSight rolled those rows up, the same target was counted more than once.

58%KPI overstatement identified and corrected

Account + monthtarget grain used in the reporting view

Employee roll-upvalidated after the dataset was corrected

SQLSnowflakeAWS QuickSightReporting GrainData Validation

The problem: the full account target was repeated for each employee

Each account had a monthly target. When more than one employee worked on the same account, that target needed to be distributed across the employees and then rolled back up correctly in the reporting hierarchy.

The old SQL attached the full account target to multiple employee rows. That meant one account target could contribute more than once when QuickSight summed the employee-level data.

Synthetic example: simplified to show the duplication

Account	Employee	Old target value	Intended share
Account A	Employee 1	1,000,000	500,000
Account A	Employee 2	1,000,000	500,000
Account A	Roll-up	2,000,000	1,000,000

Why the KPI was wrong

The dashboard was not creating the duplication by itself. QuickSight was summing the rows it received. The problem already existed in the reporting dataset because an account-level target had been repeated at the employee level.

Root cause: the target and the employee reporting rows were not aligned at the right grain before aggregation.

The fix: rebuild the reporting view around the intended grain

I reworked the SQL so the actual sales data was summarized before the targets were joined. GMV, unique orders, and items sold were aggregated by commercial account, employee assignment, and month. Monthly targets were then joined using the commercial account and month, while the company lookup was reduced to one company record per account before joining.

For accounts assigned to multiple employees, I divided the monthly account target evenly across those assigned employees before roll-up, so the employee target shares added back to the original account target.

This changed the reporting path from lower-level transactional rows to a monthly reporting view that could support the employee hierarchy without multiplying the account target.

Order-item data→Monthly actuals by account + employee→Join account target by month→QuickSight employee hierarchy→Correct roll-up

Key change: shape the data at the reporting grain first, then let QuickSight aggregate it. The fix belonged in the dataset rather than in a visual-level workaround.

The validation: employee targets had to roll up correctly

After rebuilding the view, I checked the employee-level target values and their parent totals in QuickSight. The validation was simple: the target shares shown under a parent had to add back to the correct parent target instead of multiplying it.

I also compared the corrected KPI with the expected business value to confirm that the 58% overstatement was removed.

58%

overstatement corrected after the target duplication was removed from the reporting logic.

The result: fix the dataset, not the dashboard

The issue appeared as a wrong QuickSight KPI, but the root cause was upstream in the SQL. Correcting the reporting grain gave the dashboard a dataset where account targets and employee rows could roll up correctly without compensating calculations in the visual.

This project reinforced an important reporting lesson for me: when a KPI looks wrong, check the grain and the join behavior before changing the dashboard calculation.

← Return to selected work