Forum Discussion
cumulative total
Hello,
Please help! I am trying to de-bug a measure that will calculate a cumulative total for each "Group" by "Year". Please find the .pbix file I am working on here.
Any and all guidance is welcomed.
Regards, Robert
3 Replies
hi, Anonymous
Generally speaking, calculate a cumulative total for each "Group" by "Year" use formula like below:
cumulative total = CALCULATE([Totals] , FILTER(ALLSELECTED(Years),Years[Year]<=MAX(Years[Year])))
or
cumulative total = CALCULATE([Totals] , FILTER(ALL(Years),Years[Year]<=MAX(Years[Year])))
But for your pbix file, I find that the relationship between year table and Utilization and costs table is based on Vulnerable Groups columns, this leads to a many to many relationship between them, and it could achieve cumulative total requirement.
What is the logic of it? and what is your expected output?
Best Regards,
Lin
Thanks much for your response.
I was able to get the following code to work after flattening my dataset.
**bleep** Total person-year MAX = CALCULATE([20% Implementation], FILTER(ALLSELECTED(MASTER[Year]), MASTER[Year]<= MAX(MASTER[Year])))
Now, I need to add another condition in the code so that calculated value cannot exceed the value for the "Year" or the "Total Value" for a category, whichever is lower. Any thoughts?
Robert
