Forum Discussion
Aggregating calculated data
I am tring to aggregate data I have in a column by adding the data in the current row to all the previous rows. Sample data below shows the data in the Done column that I would like to aggregate to produce what I am showing in the Cumulative Done column:
| Month | Done | Cumuluative Done |
| January | 40 | 40 |
| February | 40 | |
| March | 1 | 41 |
| April | 41 | |
| May | 27 | 68 |
| June | 67 | 135 |
| July | 12 | 147 |
| August | 49 | 196 |
| September | 113 | 309 |
| October | 115 | 424 |
| November | 83 | 507 |
| December | 507 |
For my data, the Done column is actually a calculated column based on counts of the number of items that have been moved to Done for the given month. The Cumulative Done column is to represent how many have moved to Done YTD.
How do I calculate the Cumulative Done in Power BI so I can show it in a line chart.?
15 Replies
Anonymous- see the attached file. I added a date table to your data, then used the TOTALYTD() measure, which will reset itself every January. I added some fake data into the next year so you could see how that works.
This will also work if you show the data by quarter, week, or even day.
Total Done YTD = TOTALYTD([Total Done],('Calendar'[Date]))I always recommend you create your own date table. You can see in the file how I did it in Power Query. You can add a lot more columns if you want (short month name, MMM-YY combination, quarter number, etc.)
edhans wrote:Anonymous- see the attached file. I added a date table to your data, then used the TOTALYTD() measure, which will reset itself every January. I added some fake data into the next year so you could see how that works.
This will also work if you show the data by quarter, week, or even day.
Total Done YTD = TOTALYTD([Total Done],('Calendar'[Date]))I always recommend you create your own date table. You can see in the file how I did it in Power Query. You can add a lot more columns if you want (short month name, MMM-YY combination, quarter number, etc.)
edhans, I downloaded your file but can't open it. Apparently, I need to install the latest version of Power BI but I don't have access to do that. I am a consultant and they lock this down from me. I am using Version: 2.63.3272.40262 64-bit (October 2018). Would you be able to share it in a way that I can open it with this version?
Hi Anonymous
It seems you need a measure instead of calculated column. You may add index for your data in query editor. Then use 'sort by column'. Then you may get the measure as below. Please let me know if the solution makes sense.
Cumuluative Done = CALCULATE ( SUM ( Table[Done] ), FILTER ( ALL ( Table ), Table[Index] <= MAX ( Table[Index] ) ) )Regards,
Cherie
v-cherch-msft wrote:Hi Anonymous
It seems you need a measure instead of calculated column. You may add index for your data in query editor. Then use 'sort by column'. Then you may get the measure as below. Please let me know if the solution makes sense.
Cumuluative Done = CALCULATE ( SUM ( Table[Done] ), FILTER ( ALL ( Table ), Table[Index] <= MAX ( Table[Index] ) ) )Regards,
Cherie
Cherie, to be honest, I get confused by the difference between calculated columns and measures. I believe my Done column IS a measure and that is causing a problem with the formula you suggest. I cannot choose it for this part of your formula:SUM ( Table[Done] ),
Plenty of columns show, but not my Done column.
Here is how I get my Done column: Done = CALCULATE(COUNT(Issues[Key]),History[History New Value]="Done")
Anonymous wrote:
v-cherch-msft wrote:Hi Anonymous
It seems you need a measure instead of calculated column. You may add index for your data in query editor. Then use 'sort by column'. Then you may get the measure as below. Please let me know if the solution makes sense.
Cumuluative Done = CALCULATE ( SUM ( Table[Done] ), FILTER ( ALL ( Table ), Table[Index] <= MAX ( Table[Index] ) ) )Regards,
Cherie
Cherie, to be honest, I get confused by the difference between calculated columns and measures. I believe my Done column IS a measure and that is causing a problem with the formula you suggest. I cannot choose it for this part of your formula:SUM ( Table[Done] ),
Plenty of columns show, but not my Done column.
Here is how I get my Done column: Done = CALCULATE(COUNT(Issues[Key]),History[History New Value]="Done")
Alright, so I changed my measure into a column and was able to apply your formula but I am still not getting the right results. Here is my formula:
Cumulative Done =
CALCULATE (
SUM ( Issues[Monthly Done] ),
FILTER ( ALL ( Issues ), Issues[Month #] <= MAX ( Issues[Month #] ) )
)Now it seems to just be adding them all and putting the result in every row. Here is what I am getting:
Month Month # Monthly Done Cumuluative Done January 1 40 1476 March 3 1 1476 May 5 27 1476 June 6 67 1476 July 7 12 1476 August 8 49 1476 September 9 113 1476 October 10 115 1476 November 11 83 1476
Hi,
It is quite easy to solve the problem if you have an actual date column in your data source. If that is the case, then we can use the DATESYTD() function. Share the link from where i can download your PBI file.
