How to create a measure that will sum values of zero or greater?

[mhender]

I have created measure STOCK TO XFER using measures from different tables that subtracts to provide the difference.  Have also created measure XFER PLT using STOCK TO XFER and a measure from another table to summarize in pallet quantity.  Please see syntax below and sample data.

STOCK TO XFER = [INV ON HND]-[TLT Xwk DMND]

TLT Xwk DMND = sum(OPN_SOs[Xwk DMND])

INV ON HND = sum(PLT_DTL[On Hand Qty])

XFER PLT = DIVIDE([STOCK TO XFER],SUM(SKU_Detail[PLT BLD]))

INV ON HAND	STOCK TO XFER	XFER PLT
1015	-681	-7.09
0	-384	-3.20
4345	25	.21
3360	3153	26.28
165	165	1.38

This works fine in line detail however does not make sense in the aggregate.  I need to undertand the sum of only postive values in STOCK TO XFER and XFER PLT. 

The above table totals in the below manner:

STOCK TO XFER = 2278

XFER PLT = 17.58

I need the total summation to result in this manner:

STOCK TO XFER = 3343

XFER PLT = 27.87

If any further data is need to help produce a result please ask.  Any help with this would be greatly appreciated.  

Thank you!

[FreemanZ · Accepted solution]

hi @mhender ,

Not sure if i really get you, supposing you have a SKU_Detail[sku] column, try like:

STOCK TO XFER =

SUMX(

FILTER(

    ADDCOLUMNS(

        VALUES(SKU_Detail[sku]),

        "Diff",

        [INV ON HND]-[TLT Xwk DMND]

    ),

    [Diff]>0

),

[Diff]

)

[mhender]

Hi @FreemanZ , thank you for taking the time to read and offer a solution to this question! 

Disregard:

I have used attempted solution with exact syntax.  Receiving the below error on line 6:

"The following syntax error occurred during parsing: Invalid token, Line 1, Offset 95, [Diff)))."

Based on error thinking that "Diff" needs to be defined.  How would "Diff" be defined in this solution?

Syntax error on my part!!!  @FreemanZ this worked like a charm.  Can't thank you enough for the help with this.  Cheers!