Measure based on array

[renatof]

Hi all,

how to create a measure which will give me the sum of amount column based on multiple types. 

Type	Amount
012200	206148.6
030100	62136
031100	7895
032010	105835.8
102000	5000
102000	-500
102900	-5000
102900	500
140002	52.5
140002	1973.75
140230	12663.94
220000	-262.5
220000	-9868.75
221000	-50655.7
240240	-12663.9
401400	210
430000	-206149
431200	-167972
650000	0
660000	50655.74
999990	0

For example, I want to sum all values where type begins with 4* or where types in (400000 till 499999 maybe this will be better).

I tried with manually typing every type like Four = CALCULATE(sum(Table[Amount]);filter(Table;Table[Type] = "401400" || Table[Type] = "430000")) but this is not good when I have table with 100 different types of 4. 

Best regards,

Renato Fajdiga. 

[Vvelarde]

@renatof

If Type Column is Text you can use Value to transform in Number

SUMWITH4 =
CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER (
        Table1,
        VALUE ( Table1[Type] ) >= 400000
            && VALUE ( Table1[Type] ) <= 499999
    )
)

[PavelR]

@renatof

You could try using filters on the right pane (Page level, Report level). You can define interval of Type, and when Type column is duplicated with data type text, then you also can filter as starts with 43, end with, contains etc.

And then simply have sum measure of amount column.

But unfortunately I think you can't use any easy filter directly on the page screen of PBI to this purpose, just only these filters on the right pane.

Regards.

Pavel

[renatof]

That's partly correct but I need all values for field type which starts with 2, 4 and 6. On Visual filters I have only options to put 2 filters one or (and) another.