Find average of values in dataset

[msuser48]

I have the following dataset:

employee_id	date	visits	slices	months
1	01-02-2022 00:00:00	12	1,25	11
1	22-11-2021 00:00:00	12	2,5	11
2	01-03-2022 00:00:00	21	3,5	14
2	02-06-2021 00:00:00	21	5	14
3	12-12-2022 00:00:00	30	2,25	16
3	13-12-2022 00:00:00	30	2,25	16
3	14-12-2022 00:00:00	30	2,25	16

Problem 1: How do I find the AVERAGE [visits] number for DISTINCT employee_Ids?

- Output is a measure with the number 21 in this example, since the calculation is (12+21+30) divided by 3.

- [visits] is always the same number per employee_id, so I need to somehow get DISTINCT employee_ids before I find the average.

Problem 2: How do I find the AVERAGE [slices] for each month in the [date] column for each employee_id?

- Output is a measure with the number 2,9 since the calculation is (1,25+2,5+3,5+5+2,25) divided by 5.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below measures and the attached pbix file.

Problem 1 measure: = 
AVERAGEX ( SUMMARIZE ( Data, Data[employee_id], Data[visits] ), Data[visits] )

Problem 2 measure: = 
AVERAGEX (
    SUMMARIZE (
        ADDCOLUMNS ( Data, "@month", MONTH ( Data[months] ) ),
        [@month],
        Data[slices]
    ),
    Data[slices]
)