In Wave I have a dataset where each row is a CampaignMember, but I go up through object relationships to reach Opportunities via a custom managed lookup on the Account to its first New Business opp:
ContactId.AccountId.New_Business_Opportunity__c. As part of my analysis, I need a sum of the won opportunity Amounts, but in many cases there are multiple CampaignMember rows that all relate to the same opportunity, and these multiply the resulting sum.
In native Salesforce reports (if I remember correctly) parent fields are only summed once per parent record, not once per child record. Is there any way to achieve this in a Wave dashboard?
I've already found that I can get accurate totals by grouping my Compare Table by Contact/Lead Id; I use one column to count the number of CampaignMember records for that contact or lead, and then in another formula column I can divide the Amount sum by that count and get an accurate Amount for that contact group. So then I enable "Show Totals" and the total is accurate. However I can't seem to get just that totaled number to display in a number component on my dashboard. And I have to believe there is a better way to get a sum of only unique parent records.
If there is a good way to filter out duplicate rows in a SAQL query (based on any specified field), that would solve this, I could just dedupe on the