Calculating the total for a date and the average for a specific period

[axa830]

Hello All,

 I am very new to powerBi and seem to have got stuck. My data is structured as below

What I want to achieve is get the run time for each device for a particular date. For example for device A on Jan 18th the total would come to 121. Using this total I want to subtract it from a specific number(86400) to get the idle time for that day.

My end goal is to calculate utilization of a machine for a given day or the average utilization for a period of days.

For example : if i slice my data for device A on Just 18th Jan, the utilization will be 121/86400.

                       But if i slice my data for device A  for the period Jan 18th and 19th , it would be (Average of 121 & 700)/ 86400.

Please advise as I am stuck and cant seem to calculate the averages or the total sum for a period or a single day.

[v-lili6-msft · Accepted solution]

HI, @axa830

You could use HASONEVALUE Function to create a measure like below:

Measure =
IF (
    HASONEVALUE ( 'Table'[Date Received] ),
    CALCULATE ( SUM ( 'Table'[True Run Time] ) ),
    AVERAGE ( 'Table'[True Run Time] )
)

Result:

Select one dateSelect one dateSelect multiple date

Best Regards,
Lin