cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
lucmax
Helper II
Helper II

Get sum of current plus previous rows in group

Hey,

 

I have the following input data and need to add the previous row "Value" to the last calculated value and keep getting the summation. This needs to be done grouped by the column id. The final result should be a measure which allways shows me the average sum for the Name=C starting from Rank 1.

Inputdata:

IDNameValueRank

1

A01
1B102
1C203
2A01
2B152
2B103
2C304
2D105

 

Table with summed Value (not a physical table, just to show what I mean)

IDNameValue

Value

Sum

Rank

1

A001
1B1002
1C20103
2A001
2B1502
2B10153
2C30254
2D10555

 

Result:

Average Sum for C: 17,5

 

I hope it this clear what I´m trying to do. Thank you for your help!

 

regards

lucmax

 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @lucmax 

Not completely clear. You could

1. Create a calculated column in your table:

Value Sum =
CALCULATE (
    SUM ( Table1[Value] ),
    Table1[Rank] <= EARLIER ( Table1[Rank] ) - 1,
    ALLEXCEPT ( Table1, Table1[ID] )
)

 2. Create a measure for the average:

Average val = AVERAGE(Table1[Value Sum])

 3. Set Table1[Name] in a slicer and select C, so that the measure shows the average for C

 

If you don't want to use the slicer, you could also hardcode that you want C in the measure, but i don't think that's very useful

Average val = CALCULATE( AVERAGE(Table1[Value Sum]), Table1[Name] = "C")

You could also do all this within a single measure (without the calculated column in the table) but, again, I don't think is the best way to go

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

5 REPLIES 5
AlB
Super User
Super User

@lucmax 

with a measure and using the slicer (or other)  to select the name:

Measure =
VAR auxT_ =
    ADDCOLUMNS (
        Table1,
        "ValueSum_",
            CALCULATE (
                SUM ( Table1[Value] ),
                Table1[Rank] <= EARLIER ( Table1[Rank] ) - 1,
                ALLEXCEPT ( Table1, Table1[ID] )
            )
    )
RETURN
    AVERAGEX ( auxT_, [ValueSum_] )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Again, thank you! Works like a charm.

lucmax
Helper II
Helper II

Hi @AlB ,

 

Thank you very much!

Just another question, how would I achive step 1, the calculated column, using a measure? I want to try and work without step 2 and 3 and use the measure directly in a table/matrix.

 

 

AlB
Super User
Super User

Hi @lucmax 

Not completely clear. You could

1. Create a calculated column in your table:

Value Sum =
CALCULATE (
    SUM ( Table1[Value] ),
    Table1[Rank] <= EARLIER ( Table1[Rank] ) - 1,
    ALLEXCEPT ( Table1, Table1[ID] )
)

 2. Create a measure for the average:

Average val = AVERAGE(Table1[Value Sum])

 3. Set Table1[Name] in a slicer and select C, so that the measure shows the average for C

 

If you don't want to use the slicer, you could also hardcode that you want C in the measure, but i don't think that's very useful

Average val = CALCULATE( AVERAGE(Table1[Value Sum]), Table1[Name] = "C")

You could also do all this within a single measure (without the calculated column in the table) but, again, I don't think is the best way to go

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Anonymous
Not applicable

How can I do this same thing but instead of adding the next I just want to divide the by the previus number?

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

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

Power BI Fabric Summit Carousel

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