Group BY related doubt

[ajinkya1907]

Hi,

I am using Cricket related data for one of my report. I would like to find out total centuries scored by batsmans. I am using below function but not getting desired results. Please help with other possible alternatives

Centuries = CALCULATE(COUNT(deliveries[Batsman]),FILTER(GROUPBY(deliveries,deliveries[match_id],deliveries[Batsman]),SUM(deliveries[batsman_runs])>=100))

Note - I prefer not to create separate aggregated table & would like obtain solution by calculated measures.

glimpes of dataset are below

entire data set can be found on

https://www.kaggle.com/manasgarg/ipl/data

[v-qiuyu-msft]

Hi @ajinkya1907,

You can create a measure below: 

Centuries = var temp=SUMMARIZE('deliveries','deliveries'[match_id],deliveries[batsman],"total_runs",SUM(deliveries[batsman_runs]))
return
CALCULATE(COUNT([match_id]),FILTER(temp,[total_runs]>=100))

Best Regards,
Qiuyun Yu 

[ajinkya1907]

@v-qiuyu-msft

 
Thanks for your suggestion. I tried with below calculation. summarize functionality works fine but somehow filter is not being applied in complete calculation & hence I am getting incorrect result. I tried below formula but then it gives me number of matches batsman played irrespective of score >=100

Centuries = var temp=SUMMARIZE('deliveries','deliveries'[match_id],deliveries[batsman],"total_runs",SUM(deliveries[batsman_runs]))
return
CALCULATE(COUNTROWS(temp),FILTER(temp,[total_runs]>=100))

 Also one additional scenario, I have another summary table which stores match level details & has relationship with deliveries table using ID=MatchID.

If I use your formula CALCULATE(COUNT([match_id]),FILTER(temp,[total_runs]>=100)) result remains same even if I select other sesson. But using CALCULATE(COUNTROWS(temp),FILTER(temp,[total_runs]>=100)) it at least changes (Though incorrectly as described above :-))

I can solve many more scenario once I get answer to this query. Awaiting your response. Thanks.

[ajinkya1907]

Dear @Zubair_Muhammad and @v-qiuyu-msft, Could you please help with your expertise?

[Zubair_Muhammad]

@ajinkya1907

pbix file attached here

[ajinkya1907]

@Zubair_Muhammad I have one doubt, What if I need to calculate max runs scored by any batsman using similar calculation? In other words, How to use specific column of temp table. e.g [runs] in this case. applying Max[runs] should solve the problem right?

Please note -

1) I know the answer is achievable by some other DAX formula (other than summarize or group by) but I am curious about modiying given formula, It will help me to calculate few other scenarios

2) With above formula if I simply sum the runs, It may add 2 centuries as well (If any player scores century in 2 matches, It will sum that up & show as max value)

[ajinkya1907]

Dear @Zubair_Muhammad, Did you get chance to go through this query?

[Zubair_Muhammad · Accepted solution]

@ajinkya1907

Try this MEASURE

Centuries =
VAR temp =
    FILTER (
        SUMMARIZE (
            'deliveries',
            'deliveries'[match_id],
            deliveries[batsman],
            "runs", SUM ( deliveries[batsman_runs] )
        ),
        [runs] >= 100
    )
RETURN
    COUNTROWS ( temp )

[ajinkya1907]

@Zubair_Muhammad - You are Awesome !!!

The solution worked absolutely fine. Thank you very much. This method will help me in many other calculations.

Thanks to @v-qiuyu-msft for your suggestion. It helped too 🙂

[ajinkya1907]

Hi,

I am using Cricket related data for my report. I would like to see total centuries scored by batsmans during tournament. I am using below function to avhieve the same but not getting desired result.

Centuries = CALCULATE(COUNT(deliveries[Batsman]),FILTER(GROUPBY(deliveries,deliveries[match_id],deliveries[Batsman]),SUM(deliveries[batsman_runs])>=100))

Note - I prefer not to create separate aggregated tables & check possibilities using calculated measures.

glimpes of data

entire data set can be found at below location

https://www.kaggle.com/manasgarg/ipl/data

Thanks in advance.