Forum Discussion

dinek's avatar
dinek
Frequent Visitor
8 years ago
Solved

Cummulative sum for each driver

Hello everyone!   I'm new in Power BI and I didn't find way how to make cumulative sum for each driver after every race, for each year. So, I would like to know how much points some driver had aft...
  • Anonymous's avatar
    Anonymous
    8 years ago

     

     

    I have reproduced your required table.

     

     

     

     

     

     

     

     

     

     

    To do so I have the following measure 

    Cumulative Quantity = 
IF (
    MIN ( 'DateTable'[DateID] )
        <= CALCULATE ( MAX ( Table1[DateID] ), ALL ( Table1 ) ),
    CALCULATE (
        SUM ( Table1[DriverPoints] ),
        FILTER (
            ALL ( 'DateTable'[Date] ),
            'DateTable'[Date] <= MAX ( 'DateTable'[Date] )
        )
    )
)

    This measure is esentially the same with just an extra bit to remove numbers in the future of your fact table. The important fix is to use the date in the table rather than the dateID

     

    In terms of tables I have your fact table (Table1) linked to a date table that contains the date as well as the date ID with a one to many relationship between DateTable[DateID] and Table1[DateID].

     

     

  • dinek's avatar
    dinek
    8 years ago

    Done! Thanks guys!

    CumulativePointsByYearDrivers = 
    IF (
        MIN ( 'datum'[dateId] ) <= CALCULATE ( MAX ( rezultat[dateId] ); ALL (rezultat) );
        CALCULATE (
            SUM ( rezultat[pointsDriver] );
            FILTER (
                ALL ( 'datum'[dateOfRace].[Date] );
                'datum'[dateOfRace].[Date] <= MAX ( 'datum'[dateOfRace].[Date] ) && year( datum[dateOfRace].[Date]) = year(MAX(datum[dateOfRace].[Date]))
            )
        )
    )

     

ChandeepChhabra's avatar
ChandeepChhabra
Impactful Individual
8 years ago

dinek Can you share how the expected result should look like ?

dinek's avatar
dinek
Frequent Visitor
8 years ago

Fact table (rezultat) is connected with dimensional date table (datum) by dateId.

 

Fact table example data for key atributes to make cummulate:

driverId         dateId         diverPoints

1                      100                  5       

2                      100                  3

1                      101                  5

2                      101                  4

 

Result of accumulation - what I want:

driverId          dateId        CummulativePoints

1                      100                   5

2                      100                   3

1                      101                   10

2                      101                   7

 

Hope I was clear about how expected result. I'm pretty new so don't know if this is possible to make in PowerBI.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

     

     

    I have reproduced your required table.

     

     

     

     

     

     

     

     

     

     

    To do so I have the following measure 

    Cumulative Quantity = 
IF (
    MIN ( 'DateTable'[DateID] )
        <= CALCULATE ( MAX ( Table1[DateID] ), ALL ( Table1 ) ),
    CALCULATE (
        SUM ( Table1[DriverPoints] ),
        FILTER (
            ALL ( 'DateTable'[Date] ),
            'DateTable'[Date] <= MAX ( 'DateTable'[Date] )
        )
    )
)

    This measure is esentially the same with just an extra bit to remove numbers in the future of your fact table. The important fix is to use the date in the table rather than the dateID

     

    In terms of tables I have your fact table (Table1) linked to a date table that contains the date as well as the date ID with a one to many relationship between DateTable[DateID] and Table1[DateID].

     

     

    • dinek's avatar
      dinek
      Frequent Visitor
      8 years ago

      Thank you very much! Addition what I had to make was:

      'DateTable'[Date].[Date]

      Now it works pefect!

       

      Now things started to get interesting but I have one more question. How to reset cumulative after each year? It would be perfect for drilldown graph.

      • dinek's avatar
        dinek
        Frequent Visitor
        8 years ago

        Done! Thanks guys!

        CumulativePointsByYearDrivers = 
        IF (
            MIN ( 'datum'[dateId] ) <= CALCULATE ( MAX ( rezultat[dateId] ); ALL (rezultat) );
            CALCULATE (
                SUM ( rezultat[pointsDriver] );
                FILTER (
                    ALL ( 'datum'[dateOfRace].[Date] );
                    'datum'[dateOfRace].[Date] <= MAX ( 'datum'[dateOfRace].[Date] ) && year( datum[dateOfRace].[Date]) = year(MAX(datum[dateOfRace].[Date]))
                )
            )
        )