Forum Discussion
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) 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 |
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 ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
ExcelMonkeImpactful Individual
Have you tried using Distinctcount instead of Count?
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:
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 ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.