Forum Discussion
joshua1990
Post Prodigy
5 years agoComplex SUM MAX measure
Hey community!
I have a table like this:
| Order | Date | State 1 | State 2 | Code | Quantity |
| 559955 | 01.02.2020 | 100 | 1 | X | 500 |
| 559955 | 02.02.2020 | 250 | 1 | 200 | |
| 559955 | 02.02.2020 | 250 | 2 | X | 250 |
| 559955 | 02.02.2020 | 450 | 1 | 200 | |
| 559955 | 02.02.2020 | 450 | 2 | 200 | |
| 559955 | 03.02.2020 | 600 | 2 | 450 |
This table shows me per Order level the corresponding manufacturing routing.
State 1 is the sequence for the process step and State 2 is the factor, if the step is finished (2) or is started (1).
Now I would like to get the SUM for quantity for the last process step (State 1).
That means I need to sum up the column "Quantity" per "State 1.
In addition, these filters should be set:
- Code = BLANK()
- State 1 = 450 > 600
- That means, the last step is 450 and not 600
How can this dax measure be build?
This is my first try:
1. Try =
VAR _State1 =
MAXX (
ALLSELECTED ( 'Routing' ),
'Routing'[State 1]
)
RETURN
MAXX(
FILTER(
'Routing',
'Routing'[Code] = BLANK()
&& 'Routing'[State 1] = _State1
),
'Routing'[Quantity]
)
Is an iteration function like MAXX here appropriate?
How can I add, that '450' for State1 is above '600' ?
2 Replies
- amitchandak
Super User
joshua1990 , what is expected output
- joshua1990
Post Prodigy
amitchandak : thanks! In the example above the result is = 400 (State1 = 450; 200 +200)