Need help with a calculated field - joined dataset

Question & Answer
, ,
1

Hello, below is my data from which I am trying to create a report. I tried various formula however, I am getting a wrong sum of apps in scope. Because the id field is repeated multiple times, it is taking aggregate of all fields. What I need is a total sum of anvil id/asr ids in scope for May considering the unique engagements. So for May 25, based on this table, it should be 5.

Issue is also explained here: https://stackoverflow.com/questions/57170840/quicksight-how-to-sum-values-only-from-unique-fields. But the solution provided did not work.

Screenshot 2025-06-19 at 5.17.00 PM

The solution provided did not work.

Column 1 Column 2 Column 3 Column 4
actual completion date id[engagement_report] id apps in scope
25-May 68097f0600223b2e565cf659985bd50a 7822dc9f-b765-4f9d-806d-050acb70880c 1
25-May 67edba1a00151b1f6e6928208718cd9f 42677801-97f5-4b78-9688-c6345f776079 2
25-May 67edba1a00151b1f6e6928208718cd9f edb527da-92bc-47b9-b0a0-691386bfeff7 2
25-May 67da538f0000315b4e905d8fdcc71964 28431610-b403-4e64-82ee-0c89f499c5bd 1
25-May 67da538f0000315b4e905d8fdcc71964 31cb90de-b36e-4c8d-8393-3456ff39b5d4 1
25-May 67da538f0000315b4e905d8fdcc71964 732d6ef2-bf67-4ad8-96f6-69b72bcd3714 1
25-May 67da538f0000315b4e905d8fdcc71964 fa0dba33-f974-4396-997e-61ce0f76cdab 1
25-May 67744968001ae05196a95ec8a3294fc6 10ce4799-1588-4364-b25f-35972c13480b 1
25-May 67744968001ae05196a95ec8a3294fc6 22a3bc25-091c-4dd5-abc2-651caa3c6617 1
25-May 67744968001ae05196a95ec8a3294fc6 23ff076d-f04b-4511-b695-9fb935fe0c65 1
25-May 67744968001ae05196a95ec8a3294fc6 27473584-64d3-411c-8940-d1fd71a4c298 1
25-May 67744968001ae05196a95ec8a3294fc6 814c23df-b264-4c35-8d15-8700fd12f301 1
25-May 67744968001ae05196a95ec8a3294fc6 b37e7377-644d-45f5-b390-159f5ecb026b 1
25-May 67744968001ae05196a95ec8a3294fc6 b4135227-660c-4bf4-baec-73842310d7d2 1
25-May 67744968001ae05196a95ec8a3294fc6 c5c31e5d-76b5-45b3-9e4b-fe8bfd509caa 1
25-May 67744968001ae05196a95ec8a3294fc6 cc2f7595-da20-4b32-8df8-88f1d3f3dcc1 1
2

Hi @Ami_Linish,

Try this calculated field:

sum(min({apps in scope}, [{id[engagement_report]}])

min of {apps in scope} grouped by {id[engagement_report]} essentially eliminates your duplicates. The outer sum then adds up those min values to get your desired result.

3

Hi @Ami_Linish,
It’s been awhile since we last heard from you on this thread, did you have any additional questions or did the solution provided above help with your case?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you