Forum Discussion
PowerBI Desktop (Sum If And)
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
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.
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
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...
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
5 Replies
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.
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
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...
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