Forum Discussion

AllanBerces's avatar
AllanBerces
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

Sum per Trade

Hi good day and Happy New year,

Can someone pls need assistance on how can i have the output i required. I need a sum per trade and if the sum per trade is negative then change it to zero. Please refer table below.

 

DESIRED OUTPUT

Thank you

Need Help
  • danextian's avatar
    danextian
    1 year ago

    You will need to add an indicator in your data which one is the first row for each trade value. You can add an index column in the query editor and modify your formula.

    Measure = 

VAR MinIndex =
    CALCULATE ( MIN ( 'table'[index] ), ALLEXCEPT ( 'table', 'table'[trade] ) )
RETURN
    IF (
        MIN ( 'table'[index] ) = MinIndex,
        MAX (
            0,
            -- Ensures the result is never negative by returning the higher of 0 or the calculated value.
            CALCULATE (
                -- Evaluates the specified expression in a modified filter context.
                SUM ( 'table'[progress] ),
                -- Sums the values of the 'progress' column in the 'table'.
                ALLEXCEPT (
                    'table',
                    'table'[trade]
                ) -- Removes all filters from the 'table', except those on the 'trade' column.
            )
        )
    )


Calc Column =
VAR MinIndex =
    CALCULATE ( MIN ( 'table'[index] ), ALLEXCEPT ( 'table', 'table'[trade] ) )
RETURN
    IF (
        'table'[index] = MinIndex,
        MAX (
            0,
            -- Ensures the result is never negative by returning the higher of 0 or the calculated value.
            CALCULATE (
                -- Evaluates the specified expression in a modified filter context.
                SUM ( 'table'[progress] ),
                -- Sums the values of the 'progress' column in the 'table'.
                ALLEXCEPT (
                    'table',
                    'table'[trade]
                ) -- Removes all filters from the 'table', except those on the 'trade' column.
            )
        )
    )

     

5 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi AllanBerces 

     

    Try this:

    MAX (
    0, -- Ensures the result is never negative by returning the higher of 0 or the calculated value.
    CALCULATE ( -- Evaluates the specified expression in a modified filter context.
        SUM ( 'table'[progress] ), -- Sums the values of the 'progress' column in the 'table'.
        ALLEXCEPT ( 'table', 'table'[trade] ) -- Removes all filters from the 'table', except those on the 'trade' column.
    )
)
    • AllanBerces's avatar
      AllanBerces
      Icon for Post Prodigy rankPost Prodigy
      1 year ago

      Hi danextian thank  you very much for the reply, can i know how can i add filter on the solutions you sent. let say i want only 2025 . Coz i have year column that contain 2023 to 2025 and also it is possible the Sum per Trade value will appeared only once same as below table

       

       

      Thank you

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        1 year ago

        Hi,

        It looks like you want a calculated column formula solution (not a measure).  Share data in a format that can be pasted in an MS Excel file.