Count number of centuries scored by Batsman

[sdm2211]

dear All,

I need to know how can I calculate the number of times batsman scored a century (i.e. runs more than 99)

please find attached dataset here.

https://docs.google.com/spreadsheets/d/105JwwAVlZQXZmjBx4XssVPJVd0t7yfRR/edit?usp=drive_link&ouid=11... 

[sdm2211]

@v-jiewu-msft @Greg_Deckler ,

For simplification, assume runs scored more than or equal to 5. I have made the Excel Pivot to explain my case better.

Sum of Batsman_Runs		Innings
Batsman	Match_ID	1	2	3	4	Grand Total
CT Bancroft	1	14		9		23
	3	2	2			4
	13	4				4
DA Warner	1	12		5		17
	3	13	13			26
	13	4				4
JM Anderson	13				10	10
SCJ Broad	13				4	4
UT Khawaja	13	7				7
Grand Total		56	15	14	14	99

Here we can see, 

CT Bancroft scored more than or equal to 5 runs, 2 times (Match ID 1, Innings 1 & 3)

DA Warner scored more than or equal to 5 runs, 4 times (Match ID 1, Innings 1 & 3, Match ID 3, Innings 1 & 2)

JM Anderson scored more than or equal to 5 runs, 1 time (Match ID 13, Innings 4)

UT Khawaja scored more than or equal to 5 runs, 1 time (Match ID 13, Innings 1)

Now PowerBI Matrix should show information as below.

DA Warner	4
CT Bancroft	2
JM Anderson	1
UT Khawaja	1

adding the link where you can access this information

https://drive.google.com/drive/folders/1DOSWha08loopaKXBPgx8KmFiidy4CQTm?usp=sharing 

Hope I have elaborated more.

[gmsamborn · Accepted solution]

Hi @sdm2211 

Would a measure like this help?

GT 5s = 
    COUNTROWS(
        FILTER(
            ADDCOLUMNS(
                SUMMARIZE(
                    'Table 1',
                    'Table 1'[Match_ID],
                    'Table 1'[Innings],
                    'Table 1'[Batsman]
                ),
                "_Runs", CALCULATE( SUM( 'Table 1'[Batsman_Runs] ) )
            ),
            [_Runs] >= 5
        )
    )

Let me know if you have any questions.

[sdm2211]

Hi @gmsamborn this code worked like magic 🤗

[v-jiewu-msft]

Hi ,

The file link is not opening, either upload it another way or provide the sample data in text format in your reply. Provide the expected data results as well.

Best Regards,

Wisdom Wu

[Greg_Deckler]

@sdm2211 For those of us unfamiliar with Cricket, is that per game or across multiple games? Is this for a single person (batsman) or for multiple people? If you could provide the specific details/logic around what exactly counts as a "century" that would be really helpful.

In general, however, you are going to create a table variable using SUMMARIZE. There you would group your data depending on the requirements and you would include the calculation of a column that sums up the runs. Then you can FILTER that table to where the runs are > 99 and do a COUNTROWS around that filtered table.