cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mvelazquez Frequent Visitor
Frequent Visitor

"Calculate" Formula with different filters from the same column

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 😄
2 ACCEPTED SOLUTIONS

Accepted Solutions
PANDAmonium Regular Visitor
Regular Visitor

Re: "Calculate" Formula with different filters from the same column

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)))
 

View solution in original post

Community Support Team
Community Support Team

Re: "Calculate" Formula with different filters from the same column

Hi @mvelazquez ,

 

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

Capture.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
PANDAmonium Regular Visitor
Regular Visitor

Re: "Calculate" Formula with different filters from the same column

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)))
 

View solution in original post

danextian New Contributor
New Contributor

Re: "Calculate" Formula with different filters from the same column

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
    )
)
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Community Support Team
Community Support Team

Re: "Calculate" Formula with different filters from the same column

Hi @mvelazquez ,

 

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

Capture.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,285)