Forum Discussion

BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
3 years ago

Cumulative sum by two field parameters

Hi All! In Power BI I have a visual table created like this:  Value Dimension 1 Dimension 2 0 AAA Hello1 0 AAA Hello2 0 AAA Hello3 0 AAA Hello4 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi BeaBF 

You can refer to the following solution.

The sample data is the same as you offered

1.Creata a num measure

Num =
VAR a =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table' ),
        "num", INT ( RIGHT ( 'Table'[Dimension 2], LEN ( 'Table'[Dimension 2] ) - 5 ) )
    )
RETURN
    MAXX (
        FILTER (
            a,
            [Dimension 1]
                IN VALUES ( 'Table'[Dimension 1] )
                    && [Dimension 2] IN VALUES ( 'Table'[Dimension 2] )
        ),
        [num]
    )

2.Then create the sum measure

Sum_value =
VAR a =
    CALCULATE (
        [Num],
        ALLEXCEPT ( 'Table', 'Table'[Dimension 1], 'Table'[Dimension 2] )
    )
RETURN
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Dimension 1]
                IN VALUES ( 'Table'[Dimension 1] )
                    && [Num] <= a
        ),
        [Value]
    )

Output

 

Best Regards!

Yolo Zhu

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

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    3 years ago

    Anonymous Hi!

    Thanks for the reply, but there is a problem. Dimension 1 and Dimension 2 are two different field parameters, they change dynamically based on different filters and Value is a measure, so is different from your pbix. The data provided - as specified - are from a visual table, not from the database.

    Furthermore, the cumulative sum must be continuous, it does not stop and resumes at each block of Dimension 1.

     

    BBF