How to calculate the accuracy % by using measure

[PBI_newuser]

Hi, I have the below sample data and wanted to calculate the accuracy of the estimate parts.

For example, for WO-111, the system estimate there are 3 different parts will be used but only one part (Part A) is actually used. Hence, the accuracy of the system is 1/3=33%.

If the WO Number has no Estimate parts, then we exclude them from the calculation.

If the estimate is not used in the WO, then the accuracy should be calculated as 0%.

Can anyone please advise how to come out with the measure for this "Accuracy" calculation?

The objective is to see if the accuracy improve over time.

Sample Data:

Date	WO Number	Part	Record Type
05-01-20	WO-111	A	Estimate
05-01-20	WO-111	B	Estimate
05-01-20	WO-111	C	Estimate
05-01-20	WO-111	A	Usage/Consumption
05-01-20	WO-111	D	Usage/Consumption
05-01-20	WO-111	E	Usage/Consumption
12-10-20	WO-222	A	Usage/Consumption
12-10-20	WO-222	B	Usage/Consumption
12-10-20	WO-222	C	Usage/Consumption
09-02-21	WO-333	A	Estimate
09-02-21	WO-333	B	Usage/Consumption
09-02-21	WO-333	C	Usage/Consumption
16-04-21	WO-444	E	Estimate

Expected Output:

Date	WO Number	Accuracy
05-01-20	WO-111	33%
12-10-20	WO-222	Exclude in the calculation if there is no Estimate in the same WO Number
09-02-21	WO-333	0%
16-04-21	WO-444	0%

[ryan_mayu · Accepted solution]

@PBI_newuser 

you can create a column

Column = if('Table'[Record Type]="Estimate",maxx(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])&&'Table'[WO Number]=EARLIER('Table'[WO Number])&&'Table'[Part]=EARLIER('Table'[Part])&&'Table'[Record Type]<>"Estimate"),'Table'[Record Type]))

then create a measure

Measure = 
VAR _count_estimate=CALCULATE(COUNTROWS('Table'),'Table'[Record Type]="Estimate")
VAR _count=CALCULATE(COUNTROWS('Table'),not(ISBLANK('Table'[Column])))+0
return if(ISBLANK(_count_estimate),BLANK(),DIVIDE(_count,_count_estimate))

please see the attachment below

[amitchandak]

@PBI_newuser , Try a new measure like

Measure = var _tab = FILTER(SUMMARIZE('Table', 'Table'[WO Number], 'Table'[Part], "_1", DISTINCTCOUNT('Table'[Record Type])), [_1] >=2) 
var _2  = COUNTX(VALUES('Table'[WO Number]), if(CALCULATE(DISTINCTCOUNT('Table'[Record Type]), FILTER('Table', 'Table'[Record Type] = "Estimate"))+0 >0, [WO Number], BLANK()))
return 
 DIVIDE(COUNTX(_tab,[WO Number])+0,_2)

Please find file after Signature