cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
voiciK
Frequent Visitor

Calculated measure with filter

Hi all,

 

I have a sample table like this:

 

TEST 
AccountAmount
5000 $    10.00
RES001 $    15.00
5001 $    23.00
LABOUR $    76.00
7051 $  168.00
5746 $  641.00

 

(Assuming the rows go on and on to >1000 rows with multiple values)

The account IDs are not in number format because some accounts have letters like the example above.

 

I want to create a new measure to summarise all the amounts for accounts starting with 5 (ie: 5000, 5001, etc)

 

Currently my measure looks like this:

 

Measure = CALCULATE(SUM(TEST[Amount]),TEST[Account]="5000"&&TEST[Account]="5001"&&TEST[Account]="5002" ... (and so on)

 

I wonder if there is a way to filter there 5xxx accounts in my formula? I tried TEST[Account]="5???" but it does not work.

 

Thank you

1 ACCEPTED SOLUTION
tex628
Super User
Super User

Hi @voiciK , 

Try this:

Measure = CALCULATE(SUM(TEST[Amount]),FILTER(TEST , LEFT('TEST'[ACCOUNT],1) = "5"))  

 


Connect on LinkedIn

View solution in original post

2 REPLIES 2
tex628
Super User
Super User

Hi @voiciK , 

Try this:

Measure = CALCULATE(SUM(TEST[Amount]),FILTER(TEST , LEFT('TEST'[ACCOUNT],1) = "5"))  

 


Connect on LinkedIn

View solution in original post

voiciK
Frequent Visitor

Brilliant! 

 

Thank you 😄

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors