Distinct Transaction Count with Same or Different Person

[win_anthony]

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) output. Any advice/support is greatly appreciated. Example scenario: if you were a pizza shop owner and want to know how many deliveries your drivers made, it doesn't matter the numbers of pizza's for each order. You just want to know how many times the drivers went to make the delivery. This is where I am having a hard time. 

Below is the current measure that is giving me the incorrect output

var tempCaseCount = COUNT( Table[CASE_ID] )

RETURN

CALCULATE(
    tempCaseCount
    ,FILTER(
        Table
        ,Table[Manager]
    )
)

Sample Data: 

Case_ID	Assistant	Manager
1	Jess	Sam
1	Mary	Sam
2	Jess	Mike
2	Jess	Ken

Expected (Correct) Output: Notice Sam is the sole manager but had 2 assistants. The goal is to count Sam once (1)

Manager	Case_ID_Count
Sam	1
Mike	1
Ken	1
Total	3

Current (Incorrect) Output: Notice Sam is counted for 2 but should only be counted once (1)

Manager	Case_ID_Count
Sam	2
Mike	1
Ken	1
Total	4

[v-tianyich-msft · Accepted solution]

Hi @win_anthony ,

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]

Have you tried using Distinctcount instead of Count?

[win_anthony]

@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? 

Manager	Case_ID_Count
Sam	1
Mike	1
Ken	1
Total	2

[ExcelMonke]

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: