Forum Discussion
Aggregating calculated data
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?
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
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
