Forum Discussion

voiciK's avatar
voiciK
Frequent Visitor
4 years ago
Solved

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

  • Hi voiciK , 

    Try this:

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

     

2 Replies

  • tex628's avatar
    tex628
    Community Champion

    Hi voiciK , 

    Try this:

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

     

    • voiciK's avatar
      voiciK
      Frequent Visitor

      Brilliant! 

       

      Thank you 😄