Forum Discussion

Chockers1's avatar
Chockers1
Frequent Visitor
3 years ago

Running Total

Hi ALl,

 

I have created the below DAX which calucaltes the running averages for "Bat Inns" which is everytime a batter has batted in my database, this is set as a column.

 

Bat_RunningTotal_Bat_Innings_Col =
SUMX(
    FILTER(
        ALL(Bat),
        Bat[Player ID] = EARLIER(Bat[Player ID]) && Bat[Start Date] <= EARLIER(Bat[Start Date])
    ),
    IF(ISBLANK(Bat[Bat Inns]), 0, 1)
)

 

It works great!

 

The issue is when it's in a table on a dashboard it doesn't up it as a sequnce - eg, 1, 2, 3, 4 etc... It gets show by "Start Date" in which they may have batted twice.

 

in a matrix table it doesnt show each individual "Inns" if the batter batted twice in a match, anyone know how to fix it?

 

?

 

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Chockers1 , Not very clear, But try a measure like

    Bat_RunningTotal_Bat_Innings_Col =
    SUMX(
    FILTER(
    ALL(Bat),
    Bat[Player ID] = MAx(Bat[Player ID]) && Bat[Start Date] <= MAx(Bat[Start Date])
    ),
    IF(ISBLANK(Bat[Bat Inns]), 0, 1)
    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Chockers1 ,

    Assuming you have a column [match id] in your Bat table that uniquely identifies each match, please try:

    Bat_RunningTotal_Bat_Innings_Col =
SUMX (
    FILTER (
        ALL ( Bat ),
        Bat[Player ID] = EARLIER ( Bat[Player ID] )
            && Bat[Start Date] <= EARLIER ( Bat[Start Date] )
            && (
                Bat[Start Date] < EARLIER ( Bat[Start Date] )
                    || Bat[match id] <= EARLIER ( Bat[match id] )
            )
    ),
    IF ( ISBLANK ( Bat[Bat Inns] ), 0, 1 )
)

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum