cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Memorable Member

## 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

2 REPLIES 2
Community Support

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 =
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.

Memorable Member

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors