Summing Distinct Counts

[ZSF0003]

Hello,

I have a data set where I'm returning distinct values per day, but, for the weekly total, I would like to sum up those values. Here's the visual and DAX.

And DAX

Sum of Distinct Counts =

    SUMX(

        SUMMARIZE(

            'Implementation Productivity',

            'Implementation Productivity'[CreateDt],

            'Implementation Productivity'[Branch Grouping],

            "DistinctCount",

            DISTINCTCOUNT('Implementation Productivity'[PTKey])

        ),

        [DistinctCount]

    )

As you can see, there is duplication between 'Count of Patients' and 'PT Count w/o Duplicates.' In the past, I've used DAX similar to achieve my goal but in this case, it is not working. As you can see, it mirrors the sum of non-distinct PT count. The expected value is 19 for the first row in the bottom visual because 6+4+2+3+4 = 19, then 11+20+20+21+15 = 87, and so on. The only filter I have on this sheet is a date filter that the DAX will need to react to. Any thoughts as to why the DAX is broken?

Thank you for your time

[ZSF0003]

I'm sorry. It did not solve the problem. That is why I did not accept it as a solution. 😞 Thank you for your time

[Anonymous]

Hi @ZSF0003 ,
Based on your description, I can't tell what structure your data is in. The following method is based on the example data I created.
Sample data
CategoryNameDateValue

A	A1	23/9/2024	6
A	A2	23/9/2024	11
A	A1	24/9/2024	4
A	A2	24/9/2024	20
A	A1	25/9/2024	2
A	A2	25/9/2024	20
A	A1	26/9/2024	3
A	A2	26/9/2024	21
A	A1	27/9/2024	4
A	A2	27/9/2024	15
B	B1	23/9/2024	15
B	B2	23/9/2024
B	B1	24/9/2024	3
B	B2	24/9/2024	1
B	B1	25/9/2024	10
B	B2	25/9/2024
B	B1	26/9/2024	8
B	B2	26/9/2024
B	B1	27/9/2024	6
B	B2	27/9/2024
C	C1	23/9/2024	13
C	C2	23/9/2024	9
C	C1	24/9/2024	14
C	C2	24/9/2024	8
C	C1	25/9/2024	16
C	C2	25/9/2024	5
C	C1	26/9/2024	9
C	C2	26/9/2024	3
C	C1	27/9/2024	6
C	C2	27/9/2024

Create a mesure

Total of Name = 
CALCULATE(
    SUM('Table'[Value]),
    ALLEXCEPT(
        'Table',
        'Table'[Category],
        'Table'[Name]
    )
)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly