Custom Columns

[magutter]

I'm tring to create a custom column which uses  sum(Sales) but want to filter on another column call type where it shows everything EXCEPT Types that equal "JC" or "JD". Tried the following syntax but no joyy. Can anyone help? The sum part is working OK in a previous query but I've for type, i've tried "not in", "does not contain" and "<>" but cannot get the correct syntax.

if ([Sales] > 0 and ([TYPE] <> "JC" and [TYPE] <> "JD"))
then [Sales]
else [Net Amount]

[v-jiascu-msft]

Hi @magutter,

Please try it out in this file. Which measure is the one you want?

Sum1 =
CALCULATE (
    SUM ( Table1[Sales] ),
    FILTER ( ALL ( Table1 ), NOT Table1[Type] IN { "JC", "JD" } )
)

Sum2 =
IF ( [Type] IN { "JC", "JD" }, [Net Amount], Table1[Sales] )

Sum3 =
IF (
    [Type] IN { "JC", "JD" },
    CALCULATE ( SUM ( Table1[Net Amount] ) ),
    CALCULATE (
        SUM ( Table1[Sales] ),
        FILTER ( ALL ( Table1 ), NOT Table1[Type] IN { "JC", "JD" } )
    )
)

Best Regards,

Dale

[magutter]

Hi Dale

Thanks alot for your response. I know how to do it as a measure but what would you suggest when creating a custom column in the query editor. It would appear that custom columns uses "m" query language which I'm struggling with

[v-jiascu-msft]

Which result do you want?

Best Regards,

Dale

[magutter]

Hi Dale

I want it to get the NET figure for Sales but exclude TYPE of "JC" or "JD". I want those types to return a value of 0

[v-jiascu-msft]

Hi @magutter,

You can try it like this:

if [Sales] > 0 and [Type] <> "JC" and [Type] <> "JD" 
then [Net Amount] 
else 0

Best Regards,

Dale