Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Distinct Transaction Count with Same or Different Person

The goal is to be able to count the number of transactions made by a manager regardless the number of assistants. Below is sample data which along with expected (correct) output + current (incorrect)...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    I made simple samples and you can check the results below:

    Measure = CALCULATE(DISTINCTCOUNT('Table'[Case_ID]),ALLEXCEPT('Table','Table'[Manager]))

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

ExcelMonke's avatar
ExcelMonke
Icon for Impactful Individual rankImpactful Individual
2 years ago

Have you tried using Distinctcount instead of Count?

Anonymous's avatar
Anonymous
Not applicable
2 years ago

ExcelMonke thank you for your response. Yes I have tried DistinctCount but the results was not expected. Below is the result of using Distinct Count. I am assuming that using Distinct Count is only counting the distinct case id's and disregarding the number of managers. 

Any other thoughts? 

ManagerCase_ID_Count
Sam1
Mike1
Ken1
Total2
  • ExcelMonke's avatar
    ExcelMonke
    Icon for Impactful Individual rankImpactful Individual
    2 years ago

    Based purely off of the above sample data, consider the following measure:

    Measure = 

VAR _CaseCount =
    DISTINCTCOUNT ( Sheet1[Case_ID] )
VAR _Table =
    SUMMARIZE ( Sheet1, Sheet1[Manager], "CaseCount", _CaseCount )
RETURN
    CALCULATE ( COUNTX ( _Table, [CaseCount] ) )

     

    I get the following result: