Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

Sum the Max within a grouped value

Hi there,

 

I'd like to calculate the following in a measure (see screenshot):

 

Capture.JPG

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @Anonymous ,

 

Check the measures as below.

Measure = 
var max_date = CALCULATE(MAX('Table'[date]),ALLEXCEPT('Table','Table'[plantid]))
return
IF(SELECTEDVALUE('Table'[date])=max_date,SUM('Table'[value]),BLANK())

Measure 2 = SUMX('Table',[Measure])

1.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

Hi @Anonymous ,

 

Check the measures as below.

Measure = 
var max_date = CALCULATE(MAX('Table'[date]),ALLEXCEPT('Table','Table'[plantid]))
return
IF(SELECTEDVALUE('Table'[date])=max_date,SUM('Table'[value]),BLANK())

Measure 2 = SUMX('Table',[Measure])

1.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
mahoneypat
Microsoft Employee
Microsoft Employee

Here is a measure expression that should work.

 

RecentValue =
VAR vLastValue =
    SUMX (
        VALUES ( T1[PlantID] ),
        CALCULATE (
            LASTNONBLANKVALUE (
                T1[Date],
                AVERAGE ( T1[Value] )
            )
        )
    )
RETURN
    IF (
        OR (
            NOT (
                HASONEVALUE ( T1[PlantID] )
            ),
            MAX ( T1[Date] )
                CALCULATE (
                    MAX ( T1[Date] ),
                    ALLSELECTED ( T1[Date] )
                )
        ),
        vLastValue
    )

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Hi there, thanks for your support.

Unfortunately, it does not the trick.

 

It shows me 15 on all rows where plantid is 456. I only want to see it on the row where the modification date = max date. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors