Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.?
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.
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 |
@Anonymous- I recommend you read this article on the difference between calculated columns and measures. What you are trying to do is not difficult, but I think you are missing some of the underlying concepts in how DAX works. Calculated Columns and Measures are very different in how they handle row and filter context, which are two of the hardest concepts to get your head around as a beginner. It took me a few months before I really grasped what those were doing, and it is key to just about everything you do if you are building your own formulas - be it in a table as a calculated column, or as a measure.
And unfortunately I don't have the October build of Power BI desktop to save it as an old format. I am surprised the Oct build cannot open a November file.
Do you have a personal PC you can install PBI desktop on to see how it works?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
@edhans wrote:@Anonymous- I recommend you read this article on the difference between calculated columns and measures. What you are trying to do is not difficult, but I think you are missing some of the underlying concepts in how DAX works. Calculated Columns and Measures are very different in how they handle row and filter context, which are two of the hardest concepts to get your head around as a beginner. It took me a few months before I really grasped what those were doing, and it is key to just about everything you do if you are building your own formulas - be it in a table as a calculated column, or as a measure.
And unfortunately I don't have the October build of Power BI desktop to save it as an old format. I am surprised the Oct build cannot open a November file.
Do you have a personal PC you can install PBI desktop on to see how it works?
@edhans, I agree what I am trying to do should be simple but I may be complicating get the help I need by not sharing enough detail or not having enough knowledge to explain imy situation properly. I will take a look at the article you share but I'd also like to try to share more detail to see if this becomes obvious what I need to do.
First, I took your suggestion and installed the latest version of Power BI on my personal laptop. I was then able to view your file. I think the important distinction is that you created tables with the data I shared but in my case, these are visuals I created and some of the data does not exist in tables but are generated for the visual. For example, my Done data is generated by trhis formula:
Done = CALCULATE(COUNT(Issues[Key]),History[History New Value]="Done")
The Key field in the Issues table is unique and there are over 1,400 entires in my data. The History New Value field in the History table has multiple rows for each Key and there are several possible entries in the History New Value field for each key. The Month field is displayed from a full date and time field called Updated that I set in the Values section to just show the month.
Although I have data spanning multiple years, in my visual, for the Done column, I am able to filter the display to only count the 2018 entries in the Updated field. Unfortunately, this does not seem to limit the count in the Cumulative Done field. With an earlier version of a formula I used (following), it seems to be counting all the entries across all the years:
Cumulative Done =
SUMX (
FILTER ( Issues, Issues[Month #] <= EARLIER ( Issues[Month #] ) ),
Issues[Monthly Done]
)
There does not seem to be a way to set this column to just show the 2018 cumulative counts in the filters, but I am hoping there is a minor tweak to this formula that will allow me to do it.
Hopefuly, these additional details clarifiy my issue and gives enough info to help me solve it. What do you think? Am I close, or do I need to go a whole different route?
Hi @Anonymous
Could you share your file so that we could help further on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Regards,
Cherie
Hi @Anonymous
You may create two measures as below:
Monthly Done2 = CALCULATE ( COUNT ( Issues[Key] ), History[History New Value] = "Done", YEAR ( Issues[Updated] ) = 2018, Issues[Issue Status] = "1.Done" ) + 0
Cumulative Done2 = SUMX ( FILTER ( ALL ( Issues ), Issues[Month #] <= MAX ( Issues[Month #] ) ), [Monthly Done2] )
Regards,
Cherie
@v-cherch-msft wrote:Hi @Anonymous
You may create two measures as below:
Monthly Done2 = CALCULATE ( COUNT ( Issues[Key] ), History[History New Value] = "Done", YEAR ( Issues[Updated] ) = 2018, Issues[Issue Status] = "1.Done" ) + 0Cumulative Done2 = SUMX ( FILTER ( ALL ( Issues ), Issues[Month #] <= MAX ( Issues[Month #] ) ), [Monthly Done2] )
Regards,
Cherie
Cherie, this is awesome! Thank you so much. This gives me just what I need for 2018. And it also includes the months where there were no changes, which I need. I imagine it is just a quick edit to change the Monthly Done measure to change it to 2019.
May I ask for one more suggestion? I will be sharing these reports on a published dashboard and I would like to give people the ability to set the date range so the cumulative count could start at any month, and run to any future month. Is it possible to do this with a slide bar like I used in other dashboards (which you can see in the file I shared with you)?
-Greg
Cherie, I have uploaded my data. The CFD page is where I am working on this dashboard. The Datekey table is a new one I created trying to get this to work. The other two tables are from our projects database, plus some columns and measures I have created.
Thank you for all your help with this.
-Greg
Hi @Anonymous
I cannot access the file. You can upload the .pbix file to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Regards,
Cherie
Cherie, I do not know how to mask the data, hough, I don't think anything sensitive is in it. Still, I would prefer not to have an open link posted on the board. Can you share an email so I can just send you the link? You can email me at Greg.Murphy@aew.com.
Thanks,
Greg
Hi @Anonymous
You may send the link via private message.
Regards,
Cherie
@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.)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
@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?
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |