Forum Discussion

Andy_Chandler's avatar
Andy_Chandler
Frequent Visitor
9 years ago
Solved

Calculating whether a service level has been met

I have individual rows that record the lead time for different part batches.  Each part type has a service level requirement.  If any part type fails to meet its service level, I want to calculate that the system overall has failed.

 

Part TypeLead Time AchievedLead Time TargetTarget SLA
P151051
P1101051
P1151051
P1201051
P251574
P2101574
P2151574
P2201574
P261574
P291574
P2141574
P2191574

 

In this example, P1 has 2 out of 4 batches where Lead Time meets the Lead Time Target for P1 (50%). P2 has 6 out of 8 batches (75%)  The required service level for P1 is 51%, so I want a single result to show "Failed" even though P2 has passed.

 

I can create measures to show on a visual that P1 has failed (50% < 51%) and P2 has passed (75% > 74%), but I can't work out how to calculate this as a failure overall

  • Anonymous's avatar
    Anonymous
    9 years ago

    OK, I found the flaws in my logic. My previous formula ignored row context all over the place. This version gives the results I was attempting before. I don't know if this will work in DirectQuery though, because it includes SUMMARIZE and ADDCOLUMNS. You might be able to get around that if you go into Options and check the box marked "Allow unrestricted measures in DirectQuery Mode."

     

    Pass/Fail = 
IF(
	HASONEVALUE(Parts[Part Type]),
	VAR target = MIN(Parts[Target SLA]) / 100
	VAR passfail = ADDCOLUMNS(
		SUMMARIZE(
			Parts,
			Parts[RowID]
		),
		"pass",
		CALCULATE(MIN(Parts[Lead Time Achieved]) <= MIN(Parts[Lead Time Target]))
	)
	RETURN IF(
		DIVIDE(
			COUNTROWS(FILTER(passfail, [pass] = TRUE)),
			COUNTROWS(passfail)
		) >= target,
		"Pass",
		"Fail"
	),
	VAR partscores = SUMMARIZE(
		Parts,
		Parts[Part Type],
		"score",
		VAR tg = CALCULATE(MIN(Parts[Target SLA])) / 100
		RETURN IF(
			DIVIDE(
				CALCULATE(
					COUNTROWS(
						FILTER(
							ADDCOLUMNS(
								Parts,
								"pass",
								CALCULATE(MIN(Parts[Lead Time Achieved]) <= MIN(Parts[Lead Time Target]))
							),
							[pass] = TRUE
						)
					)
				),
				CALCULATE(COUNTROWS(Parts))
			) >= tg,
			"Pass",
			"Fail"
		)
	)
	RETURN FORMAT(
		DIVIDE(
			CALCULATE(
				DISTINCTCOUNT(Parts[Part Type]),
				FILTER(partscores, [score] = "Pass")
			),
			DISTINCTCOUNT(Parts[Part Type])
		),
		"0%"
	) & " Passed"
)

14 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Just create a third measure based on the previous two,

     

    if(p1=false,false,if(p2=false,false,true)

    psuedo-code.

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Andy_Chandler

     

    1) First Create a COLUMN Yes/No

    Yes/No = IF ('Table1'[Lead Time Achieved]<='Table1'[Lead Time Target], "Yes", "No")

    2) Then these 4 MEASURES

    Target Achieved = COUNTROWS ( FILTER ('Table1', 'Table1'[Yes/No]="Yes") )

Part Transactions = CALCULATE ( COUNTROWS('Table1'),  ALLEXCEPT('Table1', 'Table1'[Part Type]) )

Rate = DIVIDE ( [Target Achieved], [Part Transactions], 0 )

Pass/Fail = IF ( [Rate] > DIVIDE( SUM(Table1[Target SLA]), [Part Transactions], 0), "Success", "Fail")

    I'm assuming Target SLA is formatted as a percentage!

     

    I wonder if the last measure Pass/Fail could be done slightly differently but basically I'm getting the average which if all numbers are the same should give you the same number at the Part Type Level!

    Anonymous how would you handle this? I wonder if there's a more efficient way?

     

    Anyway I think this should give you the results you are looking for?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      It would help if each row had a unique row identifier. You could always enter an index column in the query editor if there isn't already one that just wasn't shown in the sample data. If you had that you could do it all in one measure with no extra calculated columns.

       

      Pass/Fail = 
VAR target = MIN(Parts[Target SLA]) / 100
VAR passfail = ADDCOLUMNS(
	SUMMARIZE(
		Parts,
		Parts[RowID]
	),
	"pass",
	CALCULATE(MIN(Parts[Lead Time Achieved]) <= MIN(Parts[Lead Time Target]))
)
RETURN IF(
	DIVIDE(
		COUNTROWS(FILTER(passfail, [pass] = TRUE)),
		COUNTROWS(passfail)
	) >= target,
	"Pass",
	"Fail"
)

       

      I don't know how quickly that would work on a truly massive dataset, but if the table has a reasonable number of rows it should be fine.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        It's Friday afternoon. Time to get really goofy.

         

         

        This version returns the expected Pass/Fail score for each Part Type. But on any total/subtotal lines that represent multiple part types, it returns the percent of represented parts that passed overall:

         

        Pass/Fail = 
VAR passfail = ADDCOLUMNS(
	SUMMARIZE(
		Parts,
		Parts[RowID]
	),
	"pass",
	CALCULATE(MIN(Parts[Lead Time Achieved]) <= MIN(Parts[Lead Time Target]))
)
RETURN IF(
	HASONEVALUE(Parts[Part Type]),
	VAR target = MIN(Parts[Target SLA]) / 100
	RETURN IF(
		DIVIDE(
			COUNTROWS(FILTER(passfail, [pass] = TRUE)),
			COUNTROWS(passfail)
		) >= target,
		"Pass",
		"Fail"
	),
	VAR partscores = SUMMARIZE(
		Parts,
		Parts[Part Type],
		"score",
		VAR target = MIN(Parts[Target SLA]) / 100
		RETURN IF(
			DIVIDE(
				COUNTROWS(FILTER(passfail, [pass] = TRUE)),
				COUNTROWS(passfail)
			) >= target,
			"Pass",
			"Fail"
		)
	)
	RETURN FORMAT(
		DIVIDE(
			CALCULATE(
				DISTINCTCOUNT(Parts[Part Type]),
				FILTER(partscores, [score] = "Pass")
			),
			DISTINCTCOUNT(Parts[Part Type])
		),
		"0%"
	) & " Passed"
)