SUM and Variance calculation based on a filter in another column

[Sergi]

Hi all,

I've been using Pbi for a couple of months and now i want to make a step forward and including my own DAX formulas en my report.

I tried to calculate the variance from the data below.  (Projection - Target 21) with no sucess.  Hope some can support me with right formula.

Volumes	Data
5.000 €	Target 21
10.000 €	Target 21
30.000 €	Projection
20.000 €	Projection
4.000 €	Actual
3.500 €	Projection
5.000 €	Actual

[Sergi]

Hi Amit, 

your solution was right, my mistake with a ( position.

Many thanks!!!

[Sergi]

Hi amit,

thanks for your quick response. 

I tried your formula, but I've received this error. 

you've entered too many arguments for the function SUM. Max arguments for this function is 1.

[amitchandak · Accepted solution]

@Sergi , Try a measure like

calculate(sum(Table[Volumes]), filter(Table, Table[Data] ="Projection")) - calculate(sum(Table[Volumes]), filter(Table, Table[Data] ="Target 21"))