How to Correctly Calculate % Actual vs Target in QuickSight with Duplicated Target Data and Subtotals

[chanhlm]

Sure! Here is the English translation of your formatted question:

---

Issue with Duplicated Targets and Incorrect Subtotals When Calculating % Actual / Target in QuickSight

Data Context:

I have two tables:

• Fact table: contains actual transaction data (actual value)
• Target table: contains sales targets (target value), which is left joined to the fact table via the full name field.

Problem:

Since the target table is joined by full name, when a salesperson has multiple transactions, their target value is duplicated multiple times—once for each fact row.

---

Visual Requirement:

I’m building a pivot-style visual with two hierarchy levels:

• Level 1: Group Sales
• Level 2: Sales Name (full name)

The visual displays the following metrics:

1. Actual Value → Calculated using sum({actual}) → Correct

2. Target Value → Due to duplication, I:

   • Use max() or avg() to get the unique target per salesperson
   • For subtotal rows at the Group Sales level, I manually configure the aggregate function in the UI to use SUM instead of the default max/avg → Correct result

3. % Actual / Target → Calculated using:

   sum({actual}) / max({target})
   

   • For individual salesperson rows: correct
   • For subtotal rows (Group Sales) and Grand Total: incorrect (because max({target}) is no longer valid at that level)

---

Question:

• Is there a way to calculate % Actual / Target such that:

  • It works correctly for individual salesperson rows
  • It also works correctly for subtotal rows and the grand total

• Is there a better approach or a way to write a calculated field that automatically handles both normal and subtotal rows properly in QuickSight?

---

Let me know if you also want the solution to be:

• A single calculated field that handles all levels
• Or a separated logic for detail rows vs subtotals

---

[Giridhar.Prabhu]

Hi @chanhlm

You may need a mix of maxOver and Sum

I worked on a case sometime back where I wanted the Flight count by hour of day and then average that number when aggregated over a range of days.

Something similar should work for you

Regards,
Giri

[Xclipse]

Hi @chanhlm

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

[Xclipse]

Hi @chanhlm

Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!