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 reco...
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.