PowerBI Desktop (Sum If And)

[Dhanska]

Hi all, i did a search and can't find the answer i am looking for hence post - apologies if this is covered elsehwere.

I want a formula where a Value is returned based on several conditions;

e.g. Sum (field)Revenue where Deal = 'Won' and Year = '2019' and .....

So basically a conditional measure based on the results of other fields in teh same table

Is this possible?

Many thanks all

[AlB · Accepted solution]

Hi @Dhanska 

One option is to create a simple measure and place it in a card visual:

Measure = SUM(Table1[Revenue])

and then place the fields you want to place restrictions on in slicers. In your case a slicer on Deal, a slicer on Year, etc.

Another option is to hard-code the restrictions in the measure:

Measure2 = CALCULATE(SUM(Table1[Revenue]), Table1[Deal] = "Won", Table1[Year] = 2019) 

Please read through these posting tips. By following them, you will increase the probability of getting your questions answered quickly and you will make things much easier for people trying to help.

[Dhanska · Accepted solution]

Hi thanks so much for the quick response, much appreciated.

I am very familiar with the slicers, but the 2nd option is what i was looking for as i want to create muliple pre-build measures to add into one table.

I will give it a go.

Cheers

[Dhanska · Accepted solution]

Hi again that worked great...

If i wanted an Or in the statement so for example

Deal = "Won" or Deal = "Pending" and Year = "2019"

Please can you share the syntax for that?

Many thanks...

[AlB · Accepted solution]

Hi @Dhanska 

One option is to create a simple measure and place it in a card visual:

Measure = SUM(Table1[Revenue])

and then place the fields you want to place restrictions on in slicers. In your case a slicer on Deal, a slicer on Year, etc.

Another option is to hard-code the restrictions in the measure:

Measure2 = CALCULATE(SUM(Table1[Revenue]), Table1[Deal] = "Won", Table1[Year] = 2019) 

Please read through these posting tips. By following them, you will increase the probability of getting your questions answered quickly and you will make things much easier for people trying to help.

[Dhanska · Accepted solution]

Hi again that worked great...

If i wanted an Or in the statement so for example

Deal = "Won" or Deal = "Pending" and Year = "2019"

Please can you share the syntax for that?

Many thanks...

[AlB · Accepted solution]

Measure3 = CALCULATE(SUM(Table1[Revenue]), Table1[Deal] IN {"Won", "Pending"}, Table1[Year] = 2019) 

or equivalently:

Measure3B = CALCULATE(SUM(Table1[Revenue]), Table1[Deal] = "Won" || Table1[Deal] = "Pending" , Table1[Year] = 2019) 

or equivalently:

Measure3C = CALCULATE(SUM(Table1[Revenue]), OR(Table1[Deal] = "Won", Table1[Deal] = "Pending") , Table1[Year] = 2019) 

Please consider kudoing the posts if they are of help

Cheers

[Dhanska]

Thanks so much for your help today, it is really appreciated.

[Dhanska · Accepted solution]

Hi thanks so much for the quick response, much appreciated.

I am very familiar with the slicers, but the 2nd option is what i was looking for as i want to create muliple pre-build measures to add into one table.

I will give it a go.

Cheers