Forum Discussion

muhammedswaliha's avatar
muhammedswaliha
Regular Visitor
5 months ago

Re: Power BI- Need help with NRR Dax calculation from team1/team2 columns

Hi,

I’m currently building an IPL Points Table in Power BI using a single match-level dataset where each row contains both team1 and team2, along with their respective runs, overs, and match results.

I’ve successfully created measures for Matches (M), Wins (W), Losses (L), No Results (NR), and Points (PTS), and they are working correctly.

However, I’m facing an issue with calculating FOR, AGAINST, and Net Run Rate (NRR).

The problem is that each match stores stats separately for team1 and team2 (like team1_runs, team2_runs, team1_overs, team2_overs). While calculating NRR, I need to correctly aggregate:

  • Runs scored and overs faced by a team

  • Runs conceded and overs bowled against that team

Right now, my calculation is not correctly distinguishing between when a team appears as team1 vs team2, which is causing incorrect FOR, AGAINST, and NRR values.

To clarify:

  • If a team is in team1 → use team1_runs and team1_overs

  • If a team is in team2 → use team2_runs and team2_overs

  • Similarly, AGAINST should reverse this logic

  • After that, both sides should be summed together to get the total runs and overs for accurate calculation

I understand the logic conceptually, but I’m struggling to implement it cleanly in DAX without errors or incorrect aggregation.

What would be the best way to handle this kind of dual-column structure (team1/team2) for accurate NRR calculations in Power BI?

 

Image: without NRR  

 

 

 

Sample data:

 
match_idseason_idcitymatch_datevenuetoss_winnerteam1team2toss_decisionmatch_winnerwin_by_runswin_by_wicketsplayer_of_matchresultstageteam1_runsteam2_runsteam1_wicketsteam2_wicketsteam1_oversteam2_overssuperover_winner
14735082025New Chandigarh5/29/2025Maharaja Yadavindra Singh International Cricket Stadium, MullanpurRoyal Challengers BengaluruPunjab KingsRoyal Challengers BengalurufieldRoyal Challengers Bengaluru88475winQualifier 110110610214.110 
14857792025Jaipur5/24/2025Sawai Mansingh Stadium, JaipurDelhi CapitalsPunjab KingsDelhi CapitalsfieldDelhi Capitals 610070winUnknown206208842019.3 
14734972025Jaipur5/18/2025Sawai Mansingh Stadium, JaipurPunjab KingsPunjab KingsRajasthan RoyalsbatPunjab Kings10 3495winUnknown219209572020 
14734952025Dharamsala5/8/2025Himachal Pradesh Cricket Association Stadium, DharamsalaPunjab KingsPunjab KingsDelhi Capitalsbat    rescheduled Unknown12201010.10 
14734912025Dharamsala5/4/2025Himachal Pradesh Cricket Association Stadium, DharamsalaLucknow Super GiantsPunjab KingsLucknow Super GiantsfieldPunjab Kings37 3747winUnknown236199572020 

 

What I need:

A Points Table that shows for each team (across selected season):

 
ColumnCalculation
POSRank based on Points and NRR
TeamUnique team name (from both team1 and team2)
MCount of matches played (exclude rescheduled)
WCount of matches won (win + tie with superover)
LCount of matches lost
NRCount of abandoned or no result matches
PTS(W × 2) + (NR × 1)
FORTotal runs scored / Total overs faced (format: "2447/246.4")
AGAINSTTotal runs conceded / Total overs bowled
NRRFOR run rate - AGAINST run rate

 

 

Any guidance or best practices would be really helpful.

Thank you.

2 Replies

  • Juan-Power-bi's avatar
    Juan-Power-bi
    Icon for Super User rankSuper User
    4 months ago

    Hi my friend

    The problem here is that your data structure isn't great for this kind of calculation — having team1/team2 side by side makes DAX really awkward. The cleanest fix is to unpivot the data in Power Query first.
    Create a new table that has one row per team per match, like this:
    match_idteamruns_scoredovers_facedruns_concededovers_bowled1Punjab Kings10114.1106101RCB1061010114.1
    You can do this in Power Query by duplicating the matches table and appending two versions — one where team = team1 (using team1 stats for scored, team2 stats for conceded), and one where team = team2 (flipped). Then append both together.
    Once you have that structure, all your NRR measures become straightforward SUM operations — no more IF(team = team1, ..., ...) gymnastics in DAX.

  • AmiraBedh's avatar
    AmiraBedh
    Icon for Super User rankSuper User
    4 months ago

    Hello muhammedswaliha !

    Thank you for posting on MS Fabric community !

    I totally agree with Juan-Power-bi .

    What you have done does not work because each team stats are split across 2 different column sets so a normal measure cannot just sum one column for a team.

    So you need to create a table where each match becomes 2 rows one row for team1 and one row for team2.

    then every row already has the rest of the columns

    After that, DAX becomes simple. I created this table and you can find the solution attached in the pbix file.

    PointsTableBase =
    VAR Team1Rows =
    SELECTCOLUMNS(
    Matches,
    "match_id", Matches[match_id],
    "season_id", Matches[season_id],
    "match_date", Matches[match_date],
    "result", Matches[result],
    "stage", Matches[stage],
    "Team", Matches[team1],
    "Opponent", Matches[team2],
    "RunsScored", Matches[team1_runs],
    "OversFaced", Matches[team1_overs],
    "RunsConceded", Matches[team2_runs],
    "OversBowled", Matches[team2_overs],
    "MatchWinner", Matches[match_winner],
    "SuperOverWinner", Matches[superover_winner]
    )
    VAR Team2Rows =
    SELECTCOLUMNS(
    Matches,
    "match_id", Matches[match_id],
    "season_id", Matches[season_id],
    "match_date", Matches[match_date],
    "result", Matches[result],
    "stage", Matches[stage],
    "Team", Matches[team2],
    "Opponent", Matches[team1],
    "RunsScored", Matches[team2_runs],
    "OversFaced", Matches[team2_overs],
    "RunsConceded", Matches[team1_runs],
    "OversBowled", Matches[team1_overs],
    "MatchWinner", Matches[match_winner],
    "SuperOverWinner", Matches[superover_winner]
    )
    RETURN
    UNION(Team1Rows, Team2Rows)