Forum Discussion

magutter's avatar
magutter
Regular Visitor
8 years ago

Custom Columns

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]

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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" } )
    )
)

    Custom_Columns

     

    Best Regards,

    Dale

    • magutter's avatar
      magutter
      Regular Visitor
      8 years ago

      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