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 bu...
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

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

    Which result do you want?

     

    Best Regards,

    Dale

    • magutter's avatar
      magutter
      Regular Visitor
      8 years ago

      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's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi magutter,

         

        You can try it like this:

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

        Custom_Columns

         

        Best Regards,

        Dale