Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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% |
Solved! Go to Solution.
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
Proud to be a Super User!
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
Proud to be a Super User!
@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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |