Forum Discussion

Bansi008's avatar
Bansi008
Helper III
2 years ago
Solved

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 val...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    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.

Bansi008's avatar
Bansi008
Helper III
2 years ago

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.

DATESEC_NAMESEC_ISSUERVALUETYPETOTAL_VALUE
6/30/2023ABCAB-1Purchase4
7/31/2023ABCDAB2Expense2
8/31/2023ABCDEAB-3Purchase4
9/30/2023ABCEDFAB4Sale4
10/31/2023OPQOP5Income5
11/30/2023OPQROP6Sale6
12/31/2023OPQRSOP-7Purchase7
  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    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.