Calculated measure with filter

[voiciK]

Hi all,

I have a sample table like this:

TEST
Account	Amount
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

[tex628 · Accepted solution]

Hi @voiciK , 

Try this:

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

[voiciK]

Brilliant! 

Thank you 😄