Sum If calculation problem

[Simondeb]

Dear all,

I have trouble sutting my formul to have a sum the take into consideration my filters :

Simul2 = if(Region[Opportunity type]="Simul";

CALCULATE(sum(Region[Value Impact]);

filter(Region;Region[Region code]=EARLIER(Region[Region code]));

filter(Region;Region[Region Libelle]=SELECTEDVALUE(Region[Region Libelle]));

filter(Region;Region[Phasing]=SELECTEDVALUE(Region[Phasing]));

filter(Region;Region[Status]=SELECTEDVALUE(Region[Status])));0)

I need the opportunity type line = simul to show me the result of 608393-733960 = -125567

If I filter on Won, in need this same line to give 608393 only.

Can you help me??

Thanks you,

Simon

[dax]

Hi @Simondeb , 

According to your description, it seems that you are creating calculated column, and it seems that you want to use filter and slicer to control its value, right? 

As I know, we can't use slicer and filter to control calculated column' value. So you need to use measure to achieve this goal. By the way, if possible, could you please inform me more detailed information(such as your expected output and your sample data, you could upload virtual data instead of real data as sample)? Then I will help you more correctly.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

You are mixing column and measure calculations. You can try a measure like. You do not need handle filter unless you want change behaviour

Simul2 = 
var _sel = SELECTEDVALUE(Region[Opportunity type])

Switch(true(),
_sel="Simul"; CALCULATE(sum(Region[Value Impact]),allexcept(Region[Region code]))
_sel="Won"; CALCULATE(sum(Region[Value Impact]))
)

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Simondeb]

Hi Thanks for the tip but cannot give sample as it is professional private data...

[Greg_Deckler]

@Simondeb - We don't need the actual data, we just need enough sample/example data the emulates the problem. It's usually less than 10 rows. In table text within the forum is preferred. A link to an Excel or CSV or PBIX file next.