I have 2 tables based on cricket data. So 1 table has the data with each match id and other details. So a total of 636 matches and 638 rows. The second table has more in-depth data which shows 20 overs in each match id, each ball per over, and runs per ball per over. So if we consider 6 balls per over and a match with 20 overs, finally we have 636 matches * 20 overs * 6 balls = 76320 rows(76320 balls). including wide balls, we have another extra number of rows.
I am trying to calculate the runs per match, and then check how many centuries per match. Therefore, I need to make a new table or measure that checks, if-else statements to conditions similar to those below.
if sum(total runs) of (id)>100, then 1,
Elif sum(total runs) of (id)>200, then 2,
elif sum(total runs) of (id)>300, then 3 etc. I am writing here as an English statement.
Kindly guide with an option in power bi for the same. I have tried group by, related, calculated, etc, but possibly they are the wrong functions or maybe I am writing the syntax in the wrong way.
The below pics show the table's matches and deliveries.