calculate a subset of items

[Faberz]

Hello Everybody,

I have just started using PowerBI Desktop and DAX and I am struggling with something.

I have a table that looks like this:

Product	Revenue
Special a	1
b	2
c	3
Special d	4
Special e	5
f	6
g	7
etc.	8

I would like to calculate the revenue of all products with the attribute special, the revenue of the total revenue (all products) and the revenue of all products minus those with the label "Special".

I though of using the CALCULATE + SUM functions but I can't figure out how to limit the calculation to only the product with Special. I tried to use the FILTER funcion but with no success.

Apologies if the question is very naive. I have started using this tool a couple of week ago and I am still learning the basics.

Thank you in advance for the help.

[amitchandak · Accepted solution]

@Faberz , Try measure like

calculate(Sum(Table[Revenue]) ) - calculate(Sum(Table[Revenue]), filter(Table, Search("Special", Table[Product],,0) >0 ))

or

calculate(Sum(Table[Revenue]) , all(Table[Product]) ) - calculate(Sum(Table[Revenue]), filter(all(Table[Product]), Search("Special", Table[Product],,0) >0 ))   

[Faberz]

@amitchandak 

Thank you for the answer Man! It seems to work.

Mind if I ask you another question?

Is Search actually returning the position (mean as "row position") of the item in the product column? What is

the purpose of the ">0" at the end? 

Thank you again for the help and for making a total beginner a little less of a beginner 😉