cancel
Showing results for
Did you mean:
Highlighted
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!

1 ACCEPTED SOLUTION

Accepted Solutions
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

Proud to be a Datanaut!

7 REPLIES 7
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

Proud to be a Datanaut!

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?

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

## 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

Proud to be a Datanaut!

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?

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.

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!

Announcements

#### Community Highlights

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

#### Power Platform Summit North America

Register by September 5 to save \$200

#### 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.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 178 members 2,118 guests
Recent signins: