cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bddupre92
New Member

Need Help with Summing Values

I am trying to add up my cycle time and review time to give a column for lead time. the Cycle time is summing based on the count an Analysis (Tests) which is correct. however, the review time is doing the same, but I only need it to sum the review time for that row once, not for each count of analysis. Any help is greatly appreciated!

 

bddupre92_0-1630590083833.png

 

1 ACCEPTED SOLUTION

@bddupre92 

pls see the attachment below. Hope it's helpful.

still have a question. why the date for expected output is 2020/1/15 and 2020/1/16?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

15 REPLIES 15
ryan_mayu
Super User
Super User

@bddupre92 

what's the expected output? could you pls also provide some sample data?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ryan_mayu Yes here is another example that I hope makes more sense: 

 

on day 31 the count of Analysis (tests) is 2. the cycle time for one test is 30 so the value should be 60 which it is. however, the Review time only occurs once regardless of the amount of tests completed (Count of Analysis). the review time is 60 minutes. so the expected value is 60 however I am getting 120. 

 

Let me know if the pictures are not enough and I will do my best to post the data. sorry this is my first time on this community forum! thanks for the help!

Here is the data baswed on the above explanation of expected outcome. 

bddupre92_1-1630591667132.png

 

Here is a photo of the data in the transform data window

bddupre92_0-1630591629551.png

 

@bddupre92 

cycle time= count of analysis * 30

What's the DAX of reviewing time and lead time?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




In the Review Time column in the above picture, every row should have a value of 60. but the output I am getting is Review Time = Count Analysis*60. I only want it to show 60*1

@bddupre92 

maybe you can try distinctcount(analysis)* average(testing leadtime)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Yea still no luck. the only way to exaplain it is this way:

 

My lead time is the sum of cycle time and review time. if I have 10 samples to test my cycle time will be multiplied by 10. but I review the entire set of 10 samples one time. 

 

so if I have 10 samples, my cycle time is 10, and my review time is 50 then the value of my lead time should be Lead Time = Cycle*#samples + Review Time = (10*10) + 50 = 150 minutes. my review time column contains a lot of different values for Review time based on the specific test in the Analysis column. so averaging, using Min, or Max will not work. any suggestions is appreciated

@bddupre92 

sry, still not clear about this. Could you pls provide some sample data, not the screenshot and also provide the expected output based on your sample data?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




It will not let me post the tables for some reason. here is a link to the a file I created with Current Resutls, Expected Results, then Sample Data in that order. 

 

Let me know if this works for you

 

https://drive.google.com/file/d/1OtKhdFohu8pSIIjRxbfgLm12OUjLppz9/view?usp=sharing 

@bddupre92 

pls see the attachment below. Hope it's helpful.

still have a question. why the date for expected output is 2020/1/15 and 2020/1/16?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Sorry about that, it should have been april and august dates corressponding to the sample data. that was an error I made on accident when generating the file for you. This is exactly what I needed! thanks so much for the help and sorry for the confusion

 

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sample Data:

 

DateTesting Cycle TimeTesting Lead TimeTesting Review Time Last Analysis
4/14/2020206045 AZBR_ID_E_EP_N
4/14/2020206045 AZBR_ID_E_EP_N
4/14/2020206045 AZBR_ID_E_EP_N
4/14/2020206045 AZBR_ID_E_EP_N
4/14/2020206045 AZBR_ID_E_EP_N
8/4/2020109030 AZBR_ID_A_EP_QC9522_N
8/4/2020109030 AZBR_ID_A_EP_QC9522_N
8/4/2020109030 AZBR_ID_A_EP_QC9522_N
8/4/2020109030 AZBR_ID_A_EP_QC9522_N
8/4/2020109030 AZBR_ID_A_EP_QC9522_N
8/4/2020109030 AZBR_ID_A_EP_QC9522_N
8/4/2020109030 AZBR_ID_A_EP_QC9522_N
8/4/2020109030 AZBR_ID_A_EP_QC9522_N
8/4/2020109030 AZBR_ID_A_EP_QC9522_N
8/4/2020109030 AZBR_ID_A_EP_QC9522_N

 

Current Results: 

 

YearMonthDayCount of Product2Testing Cycle TimeTesting Lead TimeTesting Review TimeTotal TimeLast Analysis
2020April145100300225625AZBR_ID_E_EP_N
2020August4101009003001300AZBR_ID_A_EP_QC9522_N

 

Expected Result: 

 

YearMonthDayCount of Product2Testing Cycle TimeTesting Lead TimeTesting Review TimeTotal TimeLast Analysis
2020January1551006045205AZBR_ID_E_EP_N
2020January16101009030220AZBR_ID_A_EP_QC9522_N

 

Total Time = SUM(Testing Cycle Time + Testing Lead Time + Testing Review Time)

 

Let me know if this helps!

Lead Time is SUM( Cycle Time + Lead Time) 

 

Review Time should just be Distinct count of analysis*Lead time value in this case 60. 

so it should just be Lead Time = 1*60 for this case. I have list of a bunch of different lead times. 

@bddupre92 

if i didn't misunderstand your explaination.

pls try

=distinctcount(analysis)* [testing cycle time]





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




bddupre92
New Member

Here is another photo with the Sum of Testing Review Time

 

bddupre92_0-1630590360699.png

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.