Measure/ Column

[Anonymous]

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

X	USD	39486.96
X	USD	39486.96
X	USD	39486.96
X	USD	39486.96
X	USD	39486.96
X	USD	39486.96
X	USD	39486.96
Y	USD	60735.6
Y	USD	60735.6
Y	USD	60735.6
Y	USD	60735.6
Y	USD	60735.6
Y	USD	60735.6
Y	USD	60735.6

Thanks,

Amr

[v-rongtiep-msft]

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

[Greg_Deckler]

@Anonymous If you need SUMIF, you can do it like:

SUMX(FILTER('Table',[Opportunity Name] = "X"),[Value])

Excel to DAX Translation - Microsoft Power BI Community

[Sergione]

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 🙂

[amitchandak]

@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]

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.