Forum Discussion

Simondeb's avatar
Simondeb
Frequent Visitor
6 years ago

Sum If calculation problem

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
Need Help

5 Replies

    • Simondeb's avatar
      Simondeb
      Frequent Visitor
      6 years ago

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

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        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. 

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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]))
)
  • dax's avatar
    dax
    Community Support
    6 years ago

    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.