Count number of consecutive values and sequence

[Anonymous]

Hi all, 

Hopefully someone can point me in the right direction. 

I am looking to count the number of occurrences of consecutive point won within a tennis match. I would like to count how many times a player wins just 1 point, 2 points in a row, 3 points in a row, etc. 

For example:

Point won by

Player A

Player B

Player B

Player A

Player B

Player B

Player B

Here player A has won 1 point 2 times

Player B has won 2 points in a row once, and also 3 points in a row once. 

I would like to have a measure for each sequence values (ie a measure for how many times player A wins 1 point in a row, another measure for when player A wins 2 points in a row, another for how many times they win 3 points in a row, etc.

I suspect I will need to use EARLIER function and an index column but not sure how to put it all together.

Any help would be much appreciated. 

Thanks 

Marc

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the picture down below and the attached pbix file.

All steps of measures are written down in the attached file.

[CNENFRNL · Accepted solution]

Excel worksheet formula is powerful enough to deal with this simple quesiton

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the picture down below and the attached pbix file.

All steps of measures are written down in the attached file.

[Anonymous]

Hi @Jihwan_Kim and @CNENFRNL

Thank you both for your replies.... They both work great! I will now look to see if I can fully understand the workings of each solution 😊

Thanks again it's much appreciated