cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cammarcz Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User
Super User

Re: Return maximum sum of values based on criteria

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


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

7 REPLIES 7
Super User
Super User

Re: Return maximum sum of values based on criteria

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


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

cammarcz Regular Visitor
Regular Visitor

Re: Return maximum sum of values based on criteria

@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?

cammarcz Regular Visitor
Regular Visitor

Re: Return maximum sum of values based on criteria

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.

Super User
Super User

Re: Return maximum sum of values based on criteria

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.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

cammarcz Regular Visitor
Regular Visitor

Re: Return maximum sum of values based on criteria

@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

cammarcz Regular Visitor
Regular Visitor

Re: Return maximum sum of values based on criteria

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

cammarcz Regular Visitor
Regular Visitor

Re: Return maximum sum of values based on criteria

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

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 178 members 2,118 guests
Please welcome our newest community members: