Forum Discussion

Jeanxyz's avatar
Jeanxyz
Power Participant
3 years ago
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 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)
***************************
 
Need Help
  • Jeanxyz's avatar
    Jeanxyz
    3 years ago

    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

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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)
    • Jeanxyz's avatar
      Jeanxyz
      Power Participant
      3 years ago

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

  • Jeanxyz's avatar
    Jeanxyz
    Power Participant
    3 years ago

    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)