SUMX? CALCULATE? Sum with Conditions and Return Same Value by Each Opportunity

[Jungbin]

Hi all

I'd like to sum numbers with some conditions and to return that summarized number by each sales opportunity in my table.

For instance, I'll only sum opportunities that meet below conditions:

1. Product = TEST
2. Category = Negotiation, Closing
3. Period = Q4

I want a new column to be made as 'Summarized Number' in the table below. 

Can anyone help me how to solve this? I've failed with SUMX and CALCULATE...

Opportunity ID	Product	Amount	Category	Period	(Column I Want) Summarized Number
AAA	TEST	$12,000	Negotiation	Q4	$12,000
AAA	Multi	$5,000	Negotiation	Q4	$12,000
AAA	Other	$10,000	Negotiation	Q4	$12,000
BBB	Multi	$6,000	Closing	Q4	$0
BBB	Other	$2,500	Closing	Q4	$0
CCC	TEST	$8,000	Proposal	Q4	$0
DDD	Multi	$7,500	Negotiation	Q1	$0
DDD	TEST	$3,000	Negotiation	Q1	$0

[Prateek97 · Accepted solution]

Hi @Jungbin ,

Pleasae try the below code.

New Column = 

CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Opportunity ID]),'Table'[Product]="TEST", 'Table'[Category] in {"Negotiation","Closing"},'Table'[Period]="Q4")

If it works, please mark this as the solution.

Thanks

[Jungbin]

Hi Prateek97

Thanks for your help.

Could you please check your DAX once again? It returns me below error message:

The ALLEXCEPT function expects a table reference expression for argument '3', but a string or numeric expression was used.

[Prateek97]

Hi @Jungbin ,

Can you please send the DAX you have written here.

[Prateek97]

Hi @Jungbin ,

Please close the bracket after Opportunity ID highlighted in Red.

Try = CALCULATE ( SUM ( 'Table' [Amount] ), ALLEXCEPT ( 'Table' , 'Table' [Opportunity ID]), 'Table' [Product] = "TEST" , 'Table' [Category] in { "Closing" , "Negotiation" }, 'Table' [Period] = "Q4" ))

[Jungbin]

Thank you so much, Prateek97! 😁

[Ritaf1983]

Hi @Jungbin 
If I understood you right and you need this calculation row by row you can try this 2 measures :
1. 

Total Amount = SUMX('Table','Table'[Amount])

2. 

test = CALCULATE(

    [Total Amount],FILTER('Table',

    'Table'[Product]="TEST" &&'Table'[Category] in{ "Negotiation"," Closing" } && 'Table'[Period]="Q4"))

I changed 1 of the rows to have one more row that meets the conditions to insure my formula is working.

If you need other results please give more details and show a desired result

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

[Jungbin]

Hi Ritaf1983!

Thanks for your help.

From your screenshot, I want $12,000 is also filled out in the first and second row in 'test' column.

[Ritaf1983]

I think @Prateek97  suggested the right solution...

[Semaj06]

HI,

Calculate(sum(Summarized Number),

[IDPRODUCT]="TEST"

&&[Category] in{"closing","negociation"}

&&[Period]="Q4")

[Jungbin]

Hi Semaj06!

Thanks for your reply.

I think there is a misunderstanding, so I edited my post. 

Summarized Number is a new column I want. So, it can't be used in Calculate.