Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Measure/ Column

Hi everyone,

 

I need your help in the following: 

 

I want to do another column /measure to have Opportunity X sum as 39486.96 and Opportunity Y sum as 60735.6, how can I achieve that? There are a lot of opportunities though not only X and Y.

 

Opportunity NameExpected Value CurrencyExpected Value

XUSD39486.96
XUSD39486.96
XUSD39486.96
XUSD39486.96
XUSD39486.96
XUSD39486.96
XUSD39486.96
YUSD60735.6
YUSD60735.6
YUSD60735.6
YUSD60735.6
YUSD60735.6
YUSD60735.6
YUSD60735.6

 

Thanks,

Amr

Need Help
Show and Tell

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Not very clear

    if you have Expected Value

    then a measure

    sumx(summarize(Table, Table[Opportunity Name], Table[Expected Value] ), [Expected Value])

     

    If not then new column 

    Expected Value = if([Opportunity Name] = "X" , 39486.96,60735.6)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you :)) , This one worked sumx(summarize(Table, Table[Opportunity Name], Table[Expected Value] ), [Expected Value]). The problem I couldnt calculate the average of this function.

       

      Expected Value = if([Opportunity Name] = "X" , 39486.96,60735.6) --> this one assumes I only have to opportunity names, which is not the case.

       

      I have multiple opportunity names, Expected Values. 

  • Sergione's avatar
    Sergione
    Regular Visitor
    5 years ago

    Hello Anonymous,  it seems that you're looking for an equivalemnt of Excel's SUMIF. Am I right?

    I believe you'll find an answer after carefully reading this article - How CALCULATE works in DAX - SQLBI. I'm convinced that it will help you a lot in the future, so it definitely worth to invest some time in understading this material.

    Good luck with learning DAX! And let us know if you still have doubts after reading it 🙂

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    First of all, I want to know if the data you gave is the original data.

    If it did not the original data, I made an attempt with the following data.

     

    Expected Value = CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Opportunity  ]))
    Average =CALCULATE(AVERAGE('Table'[Value]),ALLEXCEPT('Table','Table'[Opportunity  ]))

     

    If it's still not the result you want, please share the raw data with me and let me know about your thoughts.

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

     

    Best Regards,
    Community Support Team _ Polly