Required help in conditional SUM function using DAX query.

[Bansi008]

Hi there,

I have given sample table for reference, using SEC_ISSUER as identifier I need to calculate sum of total value using VALUE column

for example, DAX query should sum up for all sec_name values where corrosponding sec_issuer identier found. 

TOTAL_VALUE is expected result. 

SEC_NAME	SEC_ISSUER	VALUE	TOTAL_VALUE
ABC	AB	1	10
ABCD	AB	2	10
ABCDE	AB	3	10
ABCEDF	AB	4	10
OPQ	OP	5	18
OPQR	OP	6	18
OPQRS	OP	7	18

[Bansi008]

Thanks @Ritaf1983 , this worked. I need one more help regarding the same query. Can we amend this syntax where I other issuer total value stay as it is but only purchase value will be in reverse sign. Example if sum of purchase value based on issuer is -100 then it should populate as 100 or vise versa. Rest everything should stay as it is.

DATE	SEC_NAME	SEC_ISSUER	VALUE	TYPE	TOTAL_VALUE
6/30/2023	ABC	AB	-1	Purchase	4
7/31/2023	ABCD	AB	2	Expense	2
8/31/2023	ABCDE	AB	-3	Purchase	4
9/30/2023	ABCEDF	AB	4	Sale	4
10/31/2023	OPQ	OP	5	Income	5
11/30/2023	OPQR	OP	6	Sale	6
12/31/2023	OPQRS	OP	-7	Purchase	7

[Ritaf1983]

Hi @Bansi008 
If I understood you correctly, the measure for fixed value will be :

Fixed value =

if(max('Table'[Type])="purchase" , sum('Table'[VALUE])*-1,

sum('Table'[VALUE]))

And the previous measure will change to :

sum_by_issuer =

VAR

Table_=CALCULATETABLE('Table',ALLEXCEPT('Table','Table'[SEC_ISSUER]))

RETURN

SUMX(Table_,[Fixed value])

The updated PBIX is attached

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

[Ritaf1983 · Accepted solution]

Hi @Bansi008 
You can use the measure :

sum_by_issuer = CALCULATE(

    sum('Table'[VALUE]),ALLEXCEPT('Table','Table'[SEC_ISSUER]))

The pbix is attached

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