Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 after some races. On link below is shown how much points one driver won by one race on specific date.
Solved! Go to Solution.
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].
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]))
)
)
)
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.
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].
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.
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]))
)
)
)
Cumulative Points:= CALCULATE ( SUM ( Table1[DriverPoints] ), FILTER ( ALL ( 'DateTable'[Date] ), 'DateTable'[Date] <= MAX ( 'DateTable'[Date] ) ) ) )
I would create a date table then link the date table to your fact table on the datetimeOfRace column.
Once you have that date table the above measure will calculate the cummulative points. You will need to put it in driver context by putting it in a table (or other visual) with the driver column included.
Thanks for answering but I don't know what am I doing wrong?
Relationships:
Then I wrote this formula but it's the same:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |