How to sum only negatives values from another measure

[o59393]

Hi all

I have a measure called "Delta Capacity & OU Demand" that calculates the difference between capacity & demand. Any negative value should be a lost in sales.

What I need is a measure that returns me the sum of those potential lost sales. The potential lost sales would be when demand is above capacity or in other words when the capacity saturation is >=100%

Here is the table sample. As seen below in only one month we have a saturation above 100% and it is in december 2028. The column of "delta capacity & OU demand " has a negative of 2.1 Million.

Therefore the "Potential lost sales" measure should only return the sum of the 2.1 million of lost sales.

I created the follwing measure:

Potential Lost Sales KO Demand = 

SUMX(
    SUMMARIZE(
        'SKU by line - Official',
        'SKU by line - Official'[Date],
        'SKU by line - Official'[Country Of Manufacturing],
        'SKU by line - Official'[Manufacturing plant of production],
        'SKU by line - Official'[Manufacturing line],
        'SKU by line - Official'[Package All]
    ),

IF([Saturation KO year 1]>=1,

[Delta Capacity & OU Demand],

0
)
)

The If basically says (or at least I want to have) is if the saturation on the given month is over 100% then sum the lost sales.

How ever the value i get is: 44 million. Too much!

Any idea how to get this work?

Thanks.

[AndreDeLange]

It would help to get an idea of how your data model is structured and how the measure for [Delta Capacity & OU Demand] is defined. Assuming your main table is called 'SKU by line - Official' (replace if it's something else),

Have you considered this approach:
Potential Lost sales = calculate([Delta Capacity & OU Demand],Filter('SKU by line - Official',[Delta Capacity & OU Demand]<0))

[o59393]

hi @AndreDeLange 

I got it to work with Parry's approach.

Thanks for the help!

[parry2k · Accepted solution]

@o59393 you can try this, might need further tweaking

Potential Lost Sales KO Demand = 
VAR __Table =
ADDCOLUMNS ( 
    SUMMARIZE(
        'SKU by line - Official',
        'SKU by line - Official'[Date] 
    ),
    "@Saturation KO", [Saturation KO year 1],
    "@Delta Capacity & OU Demand", [Delta Capacity & OU Demand]
)
RETURN
SUMX ( 
  FILTER( __Table, [@Saturation KO] > 1 ),
  [@Delta Capacity & OU Demand]
)

[o59393]

@parry2k  Mindblowing. 

Thanks a lot!