Hello,
I am trying to show graphically our labor cost in a 15-minute Time Interval, but the problem is, I only have the data below to work with:
| Employee | Punch In | Punch Out | Total Hours | Total Pay |
| A | 7:00:00 AM | 5:00:00 PM | 10.00 | 500.00 |
| B | 8:00:00 AM | 4:30:00 PM | 8.50 | 600.00 |
| C | 10:00:00 AM | 3:00:00 PM | 5.00 | 450.00 |
How do I go from that to below (done on Excel):
| Time Interval | Total Pay (Employee A) | Total Pay (Employee B) | Total Pay (Employee C) | Total Pay |
| 7:00:00 AM | 12.50 | - | - | 12.50 |
| 7:15:00 AM | 12.50 | - | - | 12.50 |
| 7:30:00 AM | 12.50 | - | - | 12.50 |
| 7:45:00 AM | 12.50 | - | - | 12.50 |
| 8:00:00 AM | 12.50 | 17.65 | - | 30.15 |
| 8:15:00 AM | 12.50 | 17.65 | - | 30.15 |
| 8:30:00 AM | 12.50 | 17.65 | - | 30.15 |
| 8:45:00 AM | 12.50 | 17.65 | - | 30.15 |
| 9:00:00 AM | 12.50 | 17.65 | - | 30.15 |
| 9:15:00 AM | 12.50 | 17.65 | - | 30.15 |
| 9:30:00 AM | 12.50 | 17.65 | - | 30.15 |
| 9:45:00 AM | 12.50 | 17.65 | - | 30.15 |
| 10:00:00 AM | 12.50 | 17.65 | 22.50 | 52.65 |
| 10:15:00 AM | 12.50 | 17.65 | 22.50 | 52.65 |
| 10:30:00 AM | 12.50 | 17.65 | 22.50 | 52.65 |
| 10:45:00 AM | 12.50 | 17.65 | 22.50 | 52.65 |
| 11:00:00 AM | 12.50 | 17.65 | 22.50 | 52.65 |
| 11:15:00 AM | 12.50 | 17.65 | 22.50 | 52.65 |
| 11:30:00 AM | 12.50 | 17.65 | 22.50 | 52.65 |
| 11:45:00 AM | 12.50 | 17.65 | 22.50 | 52.65 |
| 12:00:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
| 12:15:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
| 12:30:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
| 12:45:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
| 1:00:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
| 1:15:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
| 1:30:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
| 1:45:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
| 2:00:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
| 2:15:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
| 2:30:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
| 2:45:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
| 3:00:00 PM | 12.50 | 17.65 | - | 30.15 |
| 3:15:00 PM | 12.50 | 17.65 | - | 30.15 |
| 3:30:00 PM | 12.50 | 17.65 | - | 30.15 |
| 3:45:00 PM | 12.50 | 17.65 | - | 30.15 |
| 4:00:00 PM | 12.50 | 17.65 | - | 30.15 |
| 4:15:00 PM | 12.50 | 17.65 | - | 30.15 |
| 4:30:00 PM | 12.50 | - | - | 12.50 |
| 4:45:00 PM | 12.50 | - | - | 12.50 |
And eventually leads to the graph below:
Thank you very much.
Hi @Anonymous
I managed to get this going with your test data.
Step 1) Create a Time interval table in the Query Editor by creating a blank query and adding the following code (click advanced Editor and paste this code in)
let
Source = List.Generate(
()=>[i=0,j=0],
each[i] < 24,
each if [j] < 45 then
[i=[i] , j=[j] + 15]
else [i=[i]+1,j=0],each Number.ToText([i]) & ":" & Number.ToText([j])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type time}})
in
#"Changed Type"This will generate a table of times in 15 minute intervals.
Set the name of this table to be Times
Step 2) Back in Power BI, Create a New Table and use the following code
Expanded Table = SELECTCOLUMNS(
FILTER(
CROSSJOIN(Data,Times),
[Punch In]<='Times'[Column1]
&& 'Data'[Punch Out] >'Times'[Column1]
),
"Employee",[Employee],
"Time Inteval",[Column1],
"Total Pay",[Total Pay]
)This combines your existing data (which I have named as a table called Data) with the table we created in Power Query at step 1
Now add a calculated column to the Expanded Table from step 2) using this code
Interval Pay = DIVIDE(
'Expanded Table'[Total Pay],
CALCULATE(
COUNTROWS('Expanded Table'),
FILTER(ALL('Expanded Table'),
'Expanded Table'[Employee]=EARLIER('Expanded Table'[Employee])
)
)
)Now you can create a visual like the one below (or a line chart)
Hi @Anonymous
I managed to get this going with your test data.
Step 1) Create a Time interval table in the Query Editor by creating a blank query and adding the following code (click advanced Editor and paste this code in)
let
Source = List.Generate(
()=>[i=0,j=0],
each[i] < 24,
each if [j] < 45 then
[i=[i] , j=[j] + 15]
else [i=[i]+1,j=0],each Number.ToText([i]) & ":" & Number.ToText([j])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type time}})
in
#"Changed Type"This will generate a table of times in 15 minute intervals.
Set the name of this table to be Times
Step 2) Back in Power BI, Create a New Table and use the following code
Expanded Table = SELECTCOLUMNS(
FILTER(
CROSSJOIN(Data,Times),
[Punch In]<='Times'[Column1]
&& 'Data'[Punch Out] >'Times'[Column1]
),
"Employee",[Employee],
"Time Inteval",[Column1],
"Total Pay",[Total Pay]
)This combines your existing data (which I have named as a table called Data) with the table we created in Power Query at step 1
Now add a calculated column to the Expanded Table from step 2) using this code
Interval Pay = DIVIDE(
'Expanded Table'[Total Pay],
CALCULATE(
COUNTROWS('Expanded Table'),
FILTER(ALL('Expanded Table'),
'Expanded Table'[Employee]=EARLIER('Expanded Table'[Employee])
)
)
)Now you can create a visual like the one below (or a line chart)
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
