Forum Discussion

ZSF0003's avatar
ZSF0003
Helper I
1 year ago

Summing Distinct Counts

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

Need Help
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

     

  • ZSF0003's avatar
    ZSF0003
    Helper I
    1 year ago

    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