Count attempts between success, before success, average attempts between success.

[Anonymous]

Hello, Example data below

Im curious on how to count the number of "RepOuts" between the 1st success and the 2nd success, answer is 2. The data looks like this with different "ClassAccount" numbers and im looking to find an average number of "RepOut" between success. Or even how many "repout" before the 1st success. 

ClassAccount	Action	Result	Date	RepOut	Success
503207139	TH	VM	3/21/2022	1	1
503207139	TH	VM	3/17/2022	1	0
503207139	BV	$N	3/16/2022	0	0
503207139	TH	VM	3/16/2022	1	0
503207139	RN	MO	2/17/2022	0	0
503207139	RN	MO	1/17/2022	0	0
503207139	CS	SE	1/14/2022	0	0
503207139	RN	MO	1/14/2022	0	0
503207139	TH	PN	1/14/2022	1	1

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. In Power query. Add Column – Index Column – From 1.

2. Create calculated column.

Flag =
IF(
    'Table'[Success]=1&&'Table'[RepOut]=1,0,
    IF(
        'Table'[RepOut]=1&&'Table'[Success]=0,1,2))

IF =
IF(
    'Table'[Flag]=0,1,0)

group =
var _1=CALCULATE(SUM('Table'[IF]),FILTER(ALL('Table'),'Table'[ClassAccount]=EARLIER('Table'[ClassAccount])&&'Table'[Index]<EARLIER('Table'[Index])))
return
_1

failed attempts =
CALCULATE(COUNT('Table'[Flag]),FILTER(ALL('Table'),'Table'[ClassAccount]=EARLIER('Table'[ClassAccount])&&'Table'[group]=EARLIER('Table'[group])&&'Table'[Flag]=1))

the number of successes =
CALCULATE(COUNT('Table'[Flag]),FILTER(ALL('Table'),'Table'[ClassAccount]=EARLIER('Table'[ClassAccount])&&'Table'[group]=EARLIER('Table'[group])&&'Table'[Flag]=0))

average =
DIVIDE('Table'[failed attempts],'Table'[the number of successes])

3. Result:

Please click here for the pbix file

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[tamerj1]

@Anonymous 

Please try

Average Failed =
VAR Numerator =
    SUMX ( Table, IF ( Table[RepOut Success] = 0, 1, 0 ) )
VAR Denominator =
    SUM ( Table[RepOut Success] ) -1
RETURN
    DIVIDE ( Numerator, Denominator )

[tamerj1]

@Anonymous 

Can you please explain how the answer is 2?

[Anonymous]

In this case there are 2 rows where repout = 1 and success =0, which means 2 failed attempts. A successful attempt is repout=1 and success=1. Since those 2 failed attempts happened between the 1st success and the 2nd success, the answer is 2.

Im trying to find the average number of failed attempts to collected between successful attempts. 

[tamerj1]

@Anonymous 

And for each account there could be more than 2 two successful attempts?

[Anonymous]

Yes, this is just one example using 1 account with a short date range. The data has multiple accounts with multiple lines of failed attempts and successful attempts, im looking to find the average number of failed attempts between successful attempts, on the account level. 

[tamerj1]

I understand that but my question was about each account. There should be maximum of 2 successful attempts or is possible to have more than 2 successful attempts for each account. 
Also what would be the average? If the numerator is the number of attempts between successful attempts, what would be the numerator? 
how are you planning to display the result? On a card visual or on a table by account?

[Anonymous]

yes, its possible to have more than 2 successful atempts per account.

the average would be, Numerator = sum of the failed attempts between successes, divided by the denominator = number of gaps between successes? If i said that right, the denominator could also be the number of successes - 1 to remove the 1st success that didnt have a success prior to it.