Forum Discussion

ajinkya1907's avatar
ajinkya1907
Icon for Helper II rankHelper II
8 years ago
Solved

Group BY related doubt

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

 

 

Dashboards
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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 )

9 Replies

  • ajinkya1907's avatar
    ajinkya1907
    Icon for Helper II rankHelper II
    8 years ago

    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.

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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's avatar
      ajinkya1907
      Icon for Helper II rankHelper II
      8 years ago

      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.