topic Re: Count number of centuries scored by Batsman in DAX Commands and Tips

Count number of centuries scored by Batsman

sdm2211 — Tue, 04 Feb 2025 15:20:07 GMT

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=115391034353926834011&rtpof=true&sd=true

Re: Count number of centuries scored by Batsman

Greg_Deckler — Tue, 04 Feb 2025 15:26:56 GMT

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.

Re: Count number of centuries scored by Batsman

Anonymous — Wed, 05 Feb 2025 06:10:07 GMT

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

Re: Count number of centuries scored by Batsman

sdm2211 — Wed, 05 Feb 2025 20:36:31 GMT

Anonymous 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.

Re: Count number of centuries scored by Batsman

gmsamborn — Thu, 06 Feb 2025 02:08:55 GMT

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.

Re: Count number of centuries scored by Batsman

sdm2211 — Fri, 07 Feb 2025 09:04:39 GMT

Hi gmsamborn this code worked like magic 🤗