cancel
Showing results for
Did you mean:

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

Frequent Visitor

Sum the Max within a grouped value

Hi there,

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

1 ACCEPTED SOLUTION
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])``````

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.
3 REPLIES 3
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])``````

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

Frequent Visitor

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.

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