Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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.
Solved! Go to 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]
)
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
74 | |
63 | |
51 | |
48 |
User | Count |
---|---|
211 | |
86 | |
64 | |
59 | |
56 |