Software: MS Excel 2016
Here is a table
Given, there will alway be same number of triggers for a particular ID. But I don't want to count those triggers multiple times.
I tried the following DAX. My expected answer is 72, but I get 143 instead. How to fix?
Distinct_Matches:=CALCULATE(sum(Incidents[Triggers]),DISTINCT(Incidents[Incident_ID]))
Use this:
=SUMPRODUCT(B2:B7/COUNTIF(A2:A7,A2:A7))
Here is the formula with structured references:
=SUMPRODUCT(Incidents[Triggers]/COUNTIFS(Incidents[Incident_ID],Incidents[Incident_ID]))