Forum Discussion
Required help in conditional SUM function using DAX query.
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 |
3 Replies
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 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 =VARTable_=CALCULATETABLE('Table',ALLEXCEPT('Table','Table'[SEC_ISSUER]))RETURNSUMX(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.
