Return maximum sum of values based on criteria

[Anonymous]

I am working on a system to determine the manufacturing start date for new or revised parts. The new/revised parts are split up by "Simple Part" and "Assembly/Weldment".  Each part also has a routing ascoiated with it containing multiple workcenters. Each workcenter has a standard queue time ascociated with it. I need help with two problems. How do I sum the standard queue times by part? Then, once the sums are calculated I want to add the maximum for a "Simple Part" and the maximum for a "Assembly/Weldment" together to determine the total amount of days to complete manufacturing. I can not provide you with my actual data for confidentiality reasons, but I will provide a sample I made up in excel. Thank you in advance for your help!

[Greg_Deckler · Accepted solution]

Not entirely sure if this is what you want, but see if this works:

Measure = 
VAR __table = SUMMARIZE('Table 7',[Part Type],[Part Number],"__queueTime",SUM([Queue Time]))
VAR __maxSimple = MAXX(FILTER(__table,[Part Type] = "Simple Parts"),[__queueTime])
VAR __maxAssembly = MAXX(FILTER(__table,[Part Type] = "Assembly/Weldment"),[__queueTime])
RETURN
__maxSimple + __maxAssembly

Attached PBIX file, you want Table 7 and Page 3

[Anonymous]

@Greg_Deckler Nevermind I kind of got it to work. It is returning a value but it is not correct. Do you see anyting wrong with my script?

[Anonymous]

maxsimple is returning 11 when it should be 10 and maxassembly is returning 18 when it should be 9.

[Anonymous]

@Greg_Deckler I got it figured out. It was a data table relationship issue. Thank you very much for all of your help!

[Anonymous]

@Greg_Deckler This is exactly what I'm looking to do, but when I get to the second line right after "Simple Parts") queue time is not one of my options to select. Any advice?

[Anonymous]

I probably should have mentioned before that this information is being pulled from two different sources. The queue time data is coming from an excel spreadsheet and the rest of the data is being extracted from SAP.

[Greg_Deckler]

Sorry, I was being lazy. I get that option because the column is in the same table as my measure. Specify the full table and column name and that should fix your issue.