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