"Calculate" Formula with different filters from the same column

[mvelazquez]

Hello PowerBI Masters! Hope all is well.

So I have a table with different types of premiums with their respective values.

ID         Premium           Value

1        InvoicePrem         500

2          AO Prem             100

3         Inst Prem              50

4        EXP Prem             250

5          Prem                   300

What Im trying to do is basically add all the premiums for ID's only equal 1,3,4

I know I can create 3 different measures and add them up at the end or just basically use the filters tab, but is there a faster way to incorporate all the ID's within the same formula? For example something like this:

TestingPrem = CALCULATE(SUM(TRANS_BROKER[Value]),TRANS_BROKER[ID]= 1,3,4)   -----> This is giving me an error

Any help will be greatly appreciated!

Thank you 😄

[PANDAmonium · Accepted solution]

You can use CALCULATE with FITER and nested OR functions. So something like this:

Measure = CALCULATE(SUM('Table'[Column2]), FILTER('Table', OR( OR ('Table'[Column1] = 1, 'Table'[Column1] = 3), 'Table'[Column1] = 4)))

[v-frfei-msft · Accepted solution]

Hi @mvelazquez ,

Measure = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER ( 'Table', 'Table'[ID] = 1 || 'Table'[ID] = 3 || 'Table'[ID] = 4 )
)

Pbix as attached.

[danextian]

Since OR function accepts just two arguments you can use double "|" so your formula should go something like this: 

CALCULATE (
    SUM ( TRANS_BROKER[Value] ),
    FILTER (
        TRANS_BROKER,
        TRANS_BROKER[ID] = 1
            || TRANS_BROKER[ID] = 3
            || TRANS_BROKER[ID] = 4
    )
)

[PANDAmonium · Accepted solution]

You can use CALCULATE with FITER and nested OR functions. So something like this:

Measure = CALCULATE(SUM('Table'[Column2]), FILTER('Table', OR( OR ('Table'[Column1] = 1, 'Table'[Column1] = 3), 'Table'[Column1] = 4)))