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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Return maximum sum of values based on criteria

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!Example.JPG

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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?Example.JPG

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

@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
Not applicable

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.

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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