Create a calculated table to sum unique values.

[newpi]

I have a table as below.



I want to create a calculated table that calculates active score at account level for unique computer id and their max active_date. Meaning if for account 1000, computer id 1 has 2 on dates, 1-OCT and 3-OCT, I want to take value for only 3-OCT since that is the latest and sum its active_score with other computer id in the account and get result at account level.

This is what the output should look like.

There are some solutions in the community that I have tried but nothing seems to work. I can use both Power Query to create a new table or just create a new DAX table. 

I've tried summarize formula as well but it did not work. Maybe something wrong here.

[amitchandak]

@newpi , Try a measure like

calculate(sumX(values(table[computer_id]),lastnonblankvalue(active_date,max(Table[active score]))),allexcept(table, table[computer_id]))

[newpi]

@CNENFRNL Thanks. The DAX measure worked perfect. I couldn't understand the Power Query solution.

In the DAX measure, I used SUM instead of Max in the return value. Though Since its a single value per row, it doesn't make a difference I guess. Thanks a lot for your help.

Total Active Score = 
SUMX (
    VALUES ( 'DAX'[computer id] ),
    VAR __maxdate =
        CALCULATE ( MAX ( 'DAX'[date] ) )
    RETURN
        CALCULATE ( SUM ( 'DAX'[active_score] ), 'DAX'[date] = __maxdate )
)

[CNENFRNL · Accepted solution]

Hi, @newpi , whether Power Query or DAX, either can easily do the trick.

-------------------------------Power Query-------------------------------

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTIEYiMDIwNdQwNdA0OwQKwOdlljdFkjmKyBpa6RKZBjgCxrjKzXFK7XCIvJRnhljeEmG6HZi+TmWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [accountid = _t, #"computer id" = _t, date = _t, active_score = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"accountid", Int64.Type}, {"computer id", Int64.Type}, {"date", type date}, {"active_score", Int64.Type}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"accountid", "computer id"}, {{"grouped", each [maxdate = List.Max([date]), score = _{[date = maxdate]}[active_score]][score]}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"accountid"}, {{"Total_active_score", each List.Sum([grouped])}})
in
    #"Grouped Rows1"

-----------------------------DAX-----------------------------------

Total Active Score = 
SUMX (
    VALUES ( 'DAX'[computer id] ),
    VAR __maxdate =
        CALCULATE ( MAX ( 'DAX'[date] ) )
    RETURN
        CALCULATE ( MAX ( 'DAX'[active_score] ), 'DAX'[date] = __maxdate )
)