Forum Discussion

msuser48's avatar
msuser48
Icon for Helper I rankHelper I
3 years ago
Solved

Find average values in dataset when duplicate rows exist

I have the following data:

 

Issue 1) I need to find the average number of visits per employee_id.

- How do I accomplish this when there are duplicates of employee_Id?

 

Issue 2) I first need to find the AVERAGE average_time per employee_id for each MONTH using the date column. Let's call this output column average_time_per_month. Secondly, I need to find the average of average_time_per_month for all employee_ids.

- How do I accomplish this when I have days in the date column also, and not just months?

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi msuser48,

    #1, You can create a variable with summarize function to check the current value count to get average on the result if they have more than one row.

    formula =
VAR summary =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( table ),
            table[employee_id] IN VALUES ( table[employee_id] )
        ),
        [employee_id],
        [average_times],
        "CountTimes", COUNTROWS ( table )
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( summary, [CountTimes] ) ) > 0,
        CALCULATE (
            AVERAGE ( table[average_times] ),
            ALLSELECTED ( table ),
            VALUES ( table[employee_id] )
        )
    )

    #2, you can try to use the following formulas if help:

    average_time_per_month_by_employee =
VAR currDate =
    MAX ( table[date] )
RETURN
    CALCULATE (
        AVERAGE ( table[average_times] ),
        FILTER (
            ALLSELECTED ( table ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && MONTH ( [date] ) = MONTH ( currDate )
        ),
        VALUES ( table[employee_id] )
    )

average_time_per_month =
VAR summary =
    SUMMARIZE (
        ADDCOLUMNS (
            table,
            "year", YEAR ( table[date] ),
            "month", MONTH ( table[date] )
        ),
        [year],
        [month],
        [employee_id],
        "AVG", AVERAGE ( table[average_times] )
    )
RETURN
    AVERAGEX ( summary, [AVG] )

    Regards,

    Xiaoxin Sheng

3 Replies

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    3 years ago

    We want to help you but your description is too vaugue. Please write it again clearly.

     

    For example average mby year, week, quarter ????

    Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.
    Also provide the example desired output, with a clear description of the process flow.

    Remember not to share private data ... we don't want you to get into trouble. 😧

    Take care to use the same table and field names in the input, output and description so we can understand your problem and help you.

    You will get a quick response if you put time and effort into writing clear problem descriptions.

    Vaugue descriptions can waste your time and ourtime.

    Look foward to helping you when the above information is forthcoming

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi msuser48,

    #1, You can create a variable with summarize function to check the current value count to get average on the result if they have more than one row.

    formula =
VAR summary =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( table ),
            table[employee_id] IN VALUES ( table[employee_id] )
        ),
        [employee_id],
        [average_times],
        "CountTimes", COUNTROWS ( table )
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( summary, [CountTimes] ) ) > 0,
        CALCULATE (
            AVERAGE ( table[average_times] ),
            ALLSELECTED ( table ),
            VALUES ( table[employee_id] )
        )
    )

    #2, you can try to use the following formulas if help:

    average_time_per_month_by_employee =
VAR currDate =
    MAX ( table[date] )
RETURN
    CALCULATE (
        AVERAGE ( table[average_times] ),
        FILTER (
            ALLSELECTED ( table ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && MONTH ( [date] ) = MONTH ( currDate )
        ),
        VALUES ( table[employee_id] )
    )

average_time_per_month =
VAR summary =
    SUMMARIZE (
        ADDCOLUMNS (
            table,
            "year", YEAR ( table[date] ),
            "month", MONTH ( table[date] )
        ),
        [year],
        [month],
        [employee_id],
        "AVG", AVERAGE ( table[average_times] )
    )
RETURN
    AVERAGEX ( summary, [AVG] )

    Regards,

    Xiaoxin Sheng