Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost Prodigy
5 years ago

Complex SUM MAX measure

Hey community!

I have a table like this:

OrderDateState 1State 2CodeQuantity
55995501.02.20201001X500
55995502.02.20202501 200
55995502.02.20202502X250
55995502.02.20204501 200
55995502.02.20204502 200
55995503.02.20206002 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' ?