Forum Discussion

BeaBF's avatar
BeaBF
Super 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
0 AAA Hello5
0 AAA Hello6
0 AAA Hello7
0 AAA Hello8
0 AAA Hello9
0 AAA Hello10
0,00035 BBB Hello1
0,00184 BBB Hello2
0,00179 BBB Hello3
0,00211 BBB Hello4
0,00293 BBB Hello5
0,00155 BBB Hello6
0,00144 BBB Hello7
0,00085 BBB Hello8
0 BBB Hello9
0 BBB Hello10
0,00047 CCC Hello1
0,00034 CCC Hello2
0,00071 CCC Hello3
0,0004 CCC Hello4
0,00082 CCC Hello5
0,00049 CCC Hello6
0,00021 CCC Hello7
0 CCC Hello8
0 CCC Hello9
0 CCC Hello10


Value is a measure calculated in DAX, Dimension 1 is a Field Parameter of Parameter 1 and Dimension 2 is another field parameter of another Parameter 2. 
I would like to calculate the cumulative sum of Value for Dimension 1 in ascending order (alphabetical order) and Dimension 2, where Dimension 2 must be in ascending order grouped by each Dimension 1. The result to obtain is the one shown in the Result column:

Value Dimension 1 Dimension 2 Result
0 AAA Hello1 0
0 AAA Hello2 0
0 AAA Hello3 0
0 AAA Hello4 0
0 AAA Hello5 0
0 AAA Hello6 0
0 AAA Hello7 0
0 AAA Hello8 0
0 AAA Hello9 0
0 AAA Hello10 0
0,00035 BBB Hello1 0,00035
0,00184 BBB Hello2 0,00219
0,00179 BBB Hello3 0,00398
0,00211 BBB Hello4 0,00609
0,00293 BBB Hello5 0,00902
0,00155 BBB Hello6 0,01057
0,00144 BBB Hello7 0,01201
0,00085 BBB Hello8 0,01286
0 BBB Hello9 0,01286
0 BBB Hello10 0,01286
0,00047 CCC Hello1 0,01333
0,00034 CCC Hello2 0,01367
0,00071 CCC Hello3 0,01438
0,0004 CCC Hello4 0,01478
0,00082 CCC Hello5 0,0156
0,00049 CCC Hello6 0,01609
0,00021 CCC Hello7 0,0163
0 CCC Hello8 0,0163
0 CCC Hello9 0,0163
0 CCC Hello10 0,0163

 

Thanks to those who want to support me!
BBF

Need Help

2 Replies

  • 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
      Super 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