Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Getting averages from events

I'm really new to Power BI and would appreciate any assist in this. The warehouse I work in would like to have a visual report that shows the average pallet drop off time. All the information is recorded in three events; MPICK, TOPOF, and MDOFF. The UDC is the unique qualifier for every pallet. Is there a way to take the UDCs and average out the time taken?

5 Replies

  • PriyasonP's avatar
    PriyasonP
    Regular Visitor
    4 years ago

    Hi,

     

    When importing the table, ensure that the datatype of the Time Record Column is set as Duration.

     

    The default visual level average may not work for duration type in source, so dax should help. Below is the DAX formula that I tried. I have also attached a sample power bi file with the calculation done. 

     
    Average_Time_Record =
    FORMAT(
              AVERAGE('Dataset'[Time Record]),
                               "HH:MM:SS")
  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Column:

    Aver = 
TIMEVALUE (
    FORMAT (
        CALCULATE (
            AVERAGE ( 'Table'[TIME RECORD] ),
            FILTER ( 'Table', [EVENT] = EARLIER ( 'Table'[EVENT] ) )
        ),
        "hh:nn:ss"
    )
)

    This is the average calculated for the different events, is it the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I put in the code as you outlined it. It is showing an error saying the function Average cannot work with values of type String. Will the code need to be altered if the Date and Time Record columns are formats themselves? Originally the data table has a TIME column that combined the Date and Time. I created two columns DATE and TIME RECORD to have the data split.

    DATE = 
FORMAT(Table[TIME].[Date] , "mm/dd/yyyy")

Time Format = 
FORMAT(Table[TIME] , "HH:MM:SS" )

     

    • v-zhangti's avatar
      v-zhangti
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi, Anonymous 

       

      Has your problem been solved, if so, please consider Accept a correct reply as the solution. If not, please provide your actual example data, please include a screenshot of the data format. What kind of expected results do you expect? You can also show it with pictures.

       

      Best Regards,

      Community Support Team _Charlotte

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thanks for looking into this. Ultimately I am trying to get an average time in seconds for the event TOPOF. In our warehouse, a UDC will be created with the MPICK event and record the time. The next timed event that is recorded for the UDC is the TOPOF. The last timed event is MDOFF. I have been able to get the average time it takes for every UDC with the following code:

         

        Average Drop Off Time In Seconds = 
AVERAGEX (
    VALUES ( 'IN THE LAST'[UDC] ),
    CALCULATE (
        DATEDIFF ( MIN ( 'IN THE LAST'[TIME RECORD] ), MAX ( 'IN THE LAST'[TIME RECORD] ), SECOND )
    )
)

         

         The code I am attempting to use to get an average in seconds for the TOPOF event is this:

         

        Average Top Off Time in Seconds = 
TIMEVALUE (
    FORMAT (
        CALCULATE (
            AVERAGE ( 'IN THE LAST'[TIME RECORD] ),
            FILTER ( 'IN THE LAST', [EVENT] = EARLIER ( 'IN THE LAST'[EVENT] ) )
        ),
        "hh:mm:ss"
    )
)

         

        It is getting an error EARLIER/EARLIEST refers to an earlier row context which doesn't exist. I'm including a picture of the table I imported from our SQL database along with the formats and measures I'm currently using.

         

        DATE = 
FORMAT('In The Last'[TIME].[Date] , "mm/dd/yyyy")
        Time Format = 
FORMAT('In The Last'[TIME] , "HH:MM:SS" )
        Total UDCs Cancelled = 
COUNTROWS( 
    (FILTER('IN THE LAST', 'IN THE LAST'[EVENT] = "MCANC")))
        Total UDCs Worked = 
CALCULATE(
    DISTINCTCOUNT('IN THE LAST'[UDC]))

        I'm actually an IT guy and my company thinks that because I'm IT I know how to program in DAX. Appreciate all the help.

         

         

        DATE = 
FORMAT('In The Last'[TIME].[Date] , "mm/dd/yyyy")

Time Format = 
FORMAT('In The Last'[TIME] , "HH:MM:SS" )

Total UDCs Cancelled = 
COUNTROWS( 
    (FILTER('IN THE LAST', 'IN THE LAST'[EVENT] = "MCANC")))

Total UDCs Worked = 
CALCULATE(
    DISTINCTCOUNT('IN THE LAST'[UDC]))

         

        Here is the picture of the table.