Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Jeanxyz
Impactful Individual
Impactful Individual

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
Impactful Individual
Impactful Individual

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
Impactful Individual
Impactful Individual

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)
Jeanxyz
Impactful Individual
Impactful Individual

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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