cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Find average of values in dataset

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.

1 ACCEPTED SOLUTION
Super User

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]
)``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

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]
)``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors