Forum Discussion
Solved
need help with total calculation
I encounter difficulty with total calculation. I have a table where each Jira issue is assigned to a Team and a Sprint. Each sprint has a start date and end date. If an issue is solved before the spr...
I finally got it right! Here is the correct formula and toal amount matches now. The previous measure didn't work because I thought by using values() , I created a virtual table which is automatically linked to the fact table for filtering purpose. There is no relationship between the virtual table and fact table, so I need to quote the filter context explicitly in my filter condition.
story_points_completed(M) =var amt=Sumx(values(dim_IssueSprints),Calculate(sum(IssueSprints[story_points]), filter(issuesprints,IssueSprints[Status_Change_Date]>=dim_IssueSprints[Start_Date] &&IssueSprints[Status_Change_Date]<=dim_IssueSprints[End_Date] && IssueSprints[Sprint_Issue]=Dim_IssueSprints[Sprint_Issue] && IssueSprints[Issue_Status_Cat]="Done")))returnif(amt=0,blank(),amt)
hi Jeanxyz
try like:
story_points_completed(M) =
var amt=
Calculate(
sum(IssueSprints[story_points]),
IssueSprints[Issue_Status_Cat]="Done",
IssueSprints[Status_Change_Date]
>=min(IssueSprints[Start_Date]),
IssueSprints[Status_Change_Date]
<=max(IssueSprints[End_Date])
)
return
if(amt=0,blank(),amt)
This won't work because each sprint has its unique start date and end date. This needs to be taken into consideration.