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

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    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 )
    • ajinkya1907's avatar
      ajinkya1907
      Icon for Helper II rankHelper II
      8 years ago

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

      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)