Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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.
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.
@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?
@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 )
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
14 | |
14 | |
12 | |
9 |