cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## 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 sprint end date, the issue is considered completed on time. I need to add up the story points of all the issues solved on time. Below is the Dax measure I created:

For some reason the total line of story_points_completed(M) is not correct. I should have 407 points completed (i.e., sum of each spint line), but Power BI gives me 712.5 instead.

Thanks for help!

*******************

story_points_completed(M) =
var amt=
Sumx(
values(dim_IssueSprints),
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)
***************************

1 ACCEPTED SOLUTION
Post Prodigy

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"))
)
return
if(amt=0,blank(),amt)
5 REPLIES 5
Post Prodigy

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"))
)
return
if(amt=0,blank(),amt)
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Post Prodigy
Super User

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)
Post Prodigy

This won't work because each sprint has its unique start date and end date. This needs to be taken into consideration.