Find distinct order count and make some negative

[michaelho]

I have a table of sales data where each line is a line item from an order:

I'm trying to get a distinct order count (since there can be multiple lines per order) BUT I want to change the Return orders to a negative number so the end result looks something like this:

Any thoughts how this might be accomplished?

[michaelho]

Thank you, @danextian. That wasn't exactly what I was looking for, but I realize that this is probably a better solution.

[danextian · Accepted solution]

Hi @michaelho 

Try this:

Distinct Order Count =
DISTINCTCOUNT ( 'Table'[Order] )
    - CALCULATE (
        DISTINCTCOUNT ( 'Table'[Order] ),
        FILTER ( 'Table', 'Table'[Order Type] = "Returns" )
    )