cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Impactful Individual

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

Impactful Individual

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors