Hello there,
Apologies to share a link to the spreadsheet I'm referring to - Spreadsheet File
I'm new to the Data Model and Measures so the way I've designed my spreadsheet probably needs a bit of TLC.
I have a staff table and a table with how many hours each staff member has worked for the last year. The two are connected in the data model and I have made a measure that calculates the % of hours worked against FTE (full term equivalent) based on 1 FTE being 1950 hours per year (37.5 * 52).
The PivotTable, with the measure, shows the average % of each department. I need an additional column that shows the minimum % for a colleague within each department worked and an additional column that shows the maximum % for a colleague within each department.
Example - Agnieszka Cousins worked 882% and is the max for Department A, Trish Lodge 98% is the min. I'm looking to return the max and the min figures only for each department, without the colleagues listed.
What is the best way to achieve this? (Disclaimer - Data is fake/random and this is not real data)
Tusen takk! (Thank you!)
Apologies to share a link to the spreadsheet I'm referring to - Spreadsheet File
I'm new to the Data Model and Measures so the way I've designed my spreadsheet probably needs a bit of TLC.
I have a staff table and a table with how many hours each staff member has worked for the last year. The two are connected in the data model and I have made a measure that calculates the % of hours worked against FTE (full term equivalent) based on 1 FTE being 1950 hours per year (37.5 * 52).
The PivotTable, with the measure, shows the average % of each department. I need an additional column that shows the minimum % for a colleague within each department worked and an additional column that shows the maximum % for a colleague within each department.
Example - Agnieszka Cousins worked 882% and is the max for Department A, Trish Lodge 98% is the min. I'm looking to return the max and the min figures only for each department, without the colleagues listed.
What is the best way to achieve this? (Disclaimer - Data is fake/random and this is not real data)
Tusen takk! (Thank you!)
