cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
topigs_user
Frequent Visitor

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 @topigs_user ,

 

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 @topigs_user ,

 

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
Microsoft

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


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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors