Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
russell80
Helper III
Helper III

Help Grouping and Summing Latest Records

helpI have a table with the following structure:

 

CategoryColourDateValue 
DogBrown01/01/20231 
DogBlack02/01/20230 
DogYellow03/01/20230Sum this
CatWhite04/01/20231 
CatBlack05/01/20231 
CatGinger06/01/20230Sum this
MouseWhite07/01/20231 
MouseGrey08/01/20230 
MouseBlack09/01/20231Sum this

 

I'm trying to write a measure that sums the value column for the latest record of each Category. In this example the result would be 1. After much googling and chatgpt, I'm still stuck! Any help would be appreciated!

I also have a filter on the table visual that filters the date column, and I have a slicer that that slices the Colour column.

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @russell80 
Please try

ResultMeasure =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[Category],
        "@Value", SUMX ( TOPN ( 1, 'Table', 'Table'[Date] ), 'Table'[Value] )
    ),
    [@Value]
)

View solution in original post

1 REPLY 1
tamerj1
Super User
Super User

Hi @russell80 
Please try

ResultMeasure =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[Category],
        "@Value", SUMX ( TOPN ( 1, 'Table', 'Table'[Date] ), 'Table'[Value] )
    ),
    [@Value]
)

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors