How to sum a field based on a distinct count of another field

[g_hill]

Hello, I'm trying to build a measure that sums the number of children on active cases based on a table where cases can appear multiple times.

Below is a sample of the data. I want to know the total children on active cases, but I don't want to count the same case twice. The result for the sample table is 4 (Case Codes 1001 and 1003 appear twice so I don' want to count them twice).

Case Code	Children	Status
1000	1	Active
1001	2	Active
1001	2	Active
1002	3	Closed
1003	1	Active
1003	1	Active
1004	4	Closed

Here's what I've got so far but it's totalling 7 as it's counting cases more than once.

Children on Active Cases = SUMX(

    CALCULATETABLE('Table1',

    'Table1'[Status] = "Active Tenancy",

       SUMMARIZE('Table1','Table1'[Case Code])

    ),

    'Table1'[Children])

How can I ammend the measure to only look at a case once?

Thanks

[Anonymous · Accepted solution]

Try the following measure:

Children Active, Distinct =

VAR _table = SUMMARIZE(FILTER('Cases', 'Cases'[Status] = "Active"), 'Cases'[Case Code], "Child Amount", SELECTEDVALUE('Cases'[Children]))

RETURN

SUMX(_table, MAX([Child Amount]))

This is assuming that your main table is called cases, you can replace that with whatever your table is called, this worked in the sample dataset I built in power BI from the little table in your post:

The duplicate values are in there, they just don't show in visuals as they are identical:

Hope that helps, let me know if you have any issues or questions.

[g_hill]

That's working on my main data table. Thanks for your help!