Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

How to create a measure to output the sum, only if it contains certain characters

Hi there, I am stuck on creating a measure based on a column where I have to output the sum of that column whether it includes or not includes certain charachters. 

 

For example say that some of the rows in this column say 12345-NR, but some other rows in that same column just say 12345. 

 

How can I get the sum of the rows in that column that include rows only when it ends in "NR" on a seperate measure, and another seperate measure that sums the same column only when the rows do not contain "NR" at the end.

 

This is the measure (incorrect) i have created when it includes NR:

Non-recurring = CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, Backlog[Project ID] <> "NR"))
 
Atleast maybe if that measure does work, how would I use that same measure formula in a new measure and say only when it does not include NR.
 
Thanks!
1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@Anonymous 

 

 

no NR= CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, RIGHT(Backlog[Project ID],2) <> "NR"))
only NR= CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, RIGHT(Backlog[Project ID],2) = "NR"))

/if NR is contained anywhere in the string
no NR= CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, CONTAINSSTRING('Table'[Column1],"NR") =FALSE()))
only NR= CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, CONTAINSSTRING('Table'[Column1],"NR") =TRUE()))

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

3 REPLIES 3
smpa01
Super User
Super User

@Anonymous 

 

 

no NR= CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, RIGHT(Backlog[Project ID],2) <> "NR"))
only NR= CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, RIGHT(Backlog[Project ID],2) = "NR"))

/if NR is contained anywhere in the string
no NR= CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, CONTAINSSTRING('Table'[Column1],"NR") =FALSE()))
only NR= CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, CONTAINSSTRING('Table'[Column1],"NR") =TRUE()))

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

@smpa01 how are you saying "it does not contain"? I thought using "<>" meant contains

Anonymous
Not applicable

Thanks so much !

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.