Sum of latest values based on the latest distinct value of another column

[AloisOh]

Hi everyone,

For context, the title column is named after project sites and the worker and staff columns indicate the strength at the point of time. How can I sum the latest worker and staff values from all sites?

Regards,
Alois

[v-rzhou-msft · Accepted solution]

Hi @AloisOh,

As  smpa01 mentioned, we couldn't find the columns which can distinguish the time sequence, like Date or others. I think the time sequenc is distinguished according to the sequence of the same site. Here I suggest you to add an Index column by Group by Power Query.

Firstly, Group all rows by [Title] in Power Query.

 Go to Advanced editor and add a new line of code that can generate indices in all the previously grouped tables.

Indexed = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}})

Then expand your table and rename column names.

For reference: How to create group index with Power Query or R

Measure:

Sum of Latest Worker =
VAR _SUMMARIZE =
    SUMMARIZE (
        'Table',
        'Table'[Title],
        "MAXINDEX", CALCULATE ( MAX ( 'Table'[GroupIndex] ) ),
        "LATESTWORKER",
            CALCULATE (
                SUM ( 'Table'[Worker] ),
                FILTER ( 'Table', 'Table'[GroupIndex] = MAX ( 'Table'[GroupIndex] ) )
            )
    )
RETURN
    SUMX ( _SUMMARIZE, [LATESTWORKER] )

Sum of Latest Staff =
VAR _SUMMARIZE =
    SUMMARIZE (
        'Table',
        'Table'[Title],
        "MAXINDEX", CALCULATE ( MAX ( 'Table'[GroupIndex] ) ),
        "LATESTSTAFF",
            CALCULATE (
                SUM ( 'Table'[Staff] ),
                FILTER ( 'Table', 'Table'[GroupIndex] = MAX ( 'Table'[GroupIndex] ) )
            )
    )
RETURN
    SUMX ( _SUMMARIZE, [LATESTSTAFF] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@AloisOh  How can I sum the latest worker and staff values from all sites _ what indication the table has that defines latest? Is there a Date column or somehing else?

[amitchandak]

@AloisOh , do you have the site and date?

You need to try a measure like

Measure =
VAR __id = MAX ('Table'[site] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[site] = __id )
CALCULATE ( Sum ('Table'[Worker] ), VALUES ('Table'[site] ),'Table'[site] = __id,'Table'[Date] = __date )