Forum Discussion
voiciK
4 years agoFrequent 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
Hi voiciK ,
Try this:Measure = CALCULATE(SUM(TEST[Amount]),FILTER(TEST , LEFT('TEST'[ACCOUNT],1) = "5"))