Return total only per unique value (without creating a new table)

[cobwebz]

Good afternoon...I have seen many examples of this, but they all seem to involve creating a new table. We are using DirectQuery mode, so that option is apparently out.

In the example below, I simply want to get to a total enrollment result of 760, not 1,400. The 640 should not be counted twice, and that is based on the Account ID column. There is another column for "Product" (not shown due to privacy), and that is what causes multiple rows of the same Account ID to display. In this case, for example, Account ID ending in AAU has two products, but we only want to count the 640 enrollment once. And please note that this is only a snapshot of data, as we have thousands of accounts, some with several products. Users can filter down to a smaller list, but I still want to have duplicate enrollments per unique account kept out of the enrollment totals per view.

I tried using a CALCULATE with a SUM function, and then for the filter I used a DISTINCT along with the account ID, which I thought would only sum up enrollments per distinct ID. Rather, it totaled up everything anyway. I figured it wouldn't be so easy 😞 Or, maybe it is...but I haven't found the solution yet. Thanks!

[Phil_Seamark]

Hi @cobwebz

Because you are using Direct Query, perhaps use Power Query to make a copy of the table and remove duplicates.  Then you can use the new table back in Power BI Desktop to do some simple SUMs over.