cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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 5
Highlighted
Microsoft
Microsoft

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

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

Highlighted

Which result do you want?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

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

Highlighted

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors