cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculated measure with filter

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

1 ACCEPTED SOLUTION
Super User

Hi @voiciK ,

Try this:

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

2 REPLIES 2
Super User

Hi @voiciK ,

Try this:

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

Frequent Visitor

Brilliant!

Thank you 😄

Announcements