Dax Formula to get clean stop rate

[PhillipC1]

Hello,

I am trying to figure out the dax formula to utilize to obtain a clean stop rate which is number of violations divided by number of inspections. The kicker is that one inspection could have more than one violation which is where I am running into my problem. In the table below, I show some sample data where there are a total of three inspections (Distinct Report Numbers) and two violations (Violation_Count) belonging to the same inspection. In the formula I only want one violation per inspection to be counted if applicable. 

This is my current dax formula:

Clean Stop Rate = 1-CALCULATE(DIVIDE(SUM('CSA Extract'[VIOLATION_COUNT]),DISTINCTCOUNT('CSA Extract'[REPORT_NUMBER])))

This is the table for the data:

ID	REPORT_NUMBER	INSPECTION_RESULT	BASIC	CATEGORY	INSPECTION_DATE	POST_DATE	VIOLATION_COUNT
72989	6FYD0XBXFY	Violation	Vehicle Maintenance	Tires	11/11/2022	11/22/2022	1
72989	6FYD0XBXFY	Violation	Vehicle Maintenance	General Securement	11/11/2022	11/22/2022	1
72989	6C2B0XCHLG	Clean			6/24/2022	6/28/2022	0
72989	6DWE0XJSUE	Clean			8/29/2022	9/1/2022	0

My problem is that with that DAX formula, I am returning a result of 33% when I actually need to return 66% as the formula should be more along the lines of below:

3 Inspections Total

2 Inspections = Clean

1 Insepction = Violation

Meaning that 2 clean inspections divded by 3 total inspections would equal 66.66%

I feel like I am just missing something and maybe my brain is fried from the development on this dashboard, but figured I would reach out to see if anyone has a solution. 

[JamesFR06 · Accepted solution]

HI

var Clean=calculate(DISTINCTCOUNT('CSA Extract'[REPORT_NUMBER]),'CSA Extract'[INSPECTION_RESULT]="Clean")

var _Ttlinspections = calculate(DISTINCTCOUNT('CSA Extract'[REPORT_NUMBER]),all(CSA Extract))

return

divide(Clean,_Ttlinspections)

[PhillipC1]

Awesome, that did it! Thank you so much for that.