So I am building a report that shows the time spent on a job and the income the job has generated. My boss wants to see the average income of a job in hours.
Let's say three jobs have been completed: Job A: Time: 12 minutes & income 450 euro Job B: Time: 24 minutes & income 600 euro Job C: Time: 38 minutes & income 950 euro Job D: Time: 82 minutes & income 1800 euro
How do i calculate the average income per hour in PowerBI/DAX?
If you want to do it in a structured way:
DEFINE
TABLE Payroll = SELECTCOLUMNS ({
("Job-A",FORMAT(TIME(00,12,00),"HH:MM:SS"),450),
("Job-B",FORMAT(TIME(00,24,00),"HH:MM:SS"),600),
("Job-C",FORMAT(TIME(00,38,00),"HH:MM:SS"),950),
("Job-D",FORMAT(TIME(00,82,00),"HH:MM:SS"),1800)
},"Type",[Value1],"Duration",[Value2],"Income",[Value3])
EVALUATE
Payroll
Final Code:
EVALUATE
ROW (
"AVG_Earnings",
FORMAT (
ROUND (
AVERAGEX (
ADDCOLUMNS (
ADDCOLUMNS (
Payroll,
"Hour", HOUR ( Payroll[Duration] ),
"Minute", MINUTE ( Payroll[Duration] ),
"Seconds", SECOND ( Payroll[Duration] )
),
"Total",
ROUND ( [Hour] + DIVIDE ( [Minute], 60 ) + DIVIDE ( [Seconds], 3600 ), 4 )
),
DIVIDE ( [Income], [Total] )
),
2
),
"Currency",
"de-DE"
)
)
