Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Jeanxyz
Post Prodigy
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)
***************************
values() total.png
 
1 ACCEPTED SOLUTION
Jeanxyz
Post Prodigy
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)

View solution in original post

5 REPLIES 5
Jeanxyz
Post Prodigy
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)
Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

FreemanZ
Super User
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)

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.