topic Re: Count attempts between success, before success, average attempts between success. in DAX Commands and Tips

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

Anonymous — Wed, 13 Apr 2022 18:36:27 GMT

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

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

tamerj1 — Wed, 13 Apr 2022 18:55:45 GMT

Anonymous

Can you please explain how the answer is 2?

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

Anonymous — Wed, 13 Apr 2022 19:02:48 GMT

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.

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

tamerj1 — Wed, 13 Apr 2022 19:03:17 GMT

Anonymous

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

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

Anonymous — Wed, 13 Apr 2022 19:06:02 GMT

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.

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

tamerj1 — Wed, 13 Apr 2022 19:15:07 GMT

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?

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

Anonymous — Wed, 13 Apr 2022 19:21:08 GMT

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.

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

tamerj1 — Wed, 13 Apr 2022 20:08:17 GMT

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 )

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

Anonymous — Tue, 19 Apr 2022 05:03:20 GMT

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