Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
msuser48
Helper I
Helper I

Find average of values in dataset

I have the following dataset:

 

employee_iddatevisitsslicesmonths
101-02-2022 00:00:00121,2511
122-11-2021 00:00:00122,511
201-03-2022 00:00:00213,514
202-06-2021 00:00:0021514
312-12-2022 00:00:00302,2516
313-12-2022 00:00:00302,2516
314-12-2022 00:00:00302,2516

 

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
Jihwan_Kim
Super User
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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.