Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys, first time posting so please forgive me if I'm not very clear!
Dummy data and issue shown below.
Date | WK Start Date | Player | Category | Value |
28/09/2021 | 25/09/2021 | A | 1 | 2000 |
28/09/2021 | 25/09/2021 | B | 1 | 5000 |
27/09/2021 | 25/09/2021 | A | 1 | 3000 |
27/09/2021 | 25/09/2021 | B | 1 | 4000 |
So first I want a measure to SUM the week (based on week start date) e.g. expected result for Player A is a sum of 5000 for week beginning on the 25th. Then I want a measure for the Category AVERAGE for that week e.g. expected result is players Bs sum being 9000 for the same week, so the average for Category 1 would be 7000. The full data set will include multiple rows of data over many dates and week start dates, and each player will have an assigned category.
Any ideas on what the Dax measures (not columns) would look like for these measures?
Thanks in advance!
Logan
Solved! Go to Solution.
@LS-PScience Hi LS-PScience,
Please try belwo two measures.
CategoryAvg =
@LS-PScience Hi LS-PScience,
Please try belwo two measures.
CategoryAvg =
Hi @LS-PScience
Please try these measures:
Sum Week:
Sum Week =
VAR _Week =
MAX ( 'Table'[WK Start Date] )
RETURN
CALCULATE (
SUM ( 'Table'[Value] ),
ALLEXCEPT ( 'Table', 'Table'[WK Start Date], 'Table'[Player] )
)
Average:
Average =
VAR _SumCat =
CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Category] ) )
VAR _CountPlayer =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Player] ),
ALLEXCEPT ( 'Table', 'Table'[Category] )
)
RETURN
_SumCat / _CountPlayer
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos🙏!!
Hey VahidDM
I've replicated your measures but it didn't quite work as I'd hoped unfortunately.
It appears that with more than 1 week start dates (or maybe its the multiple categories messing it up), it would just sum everything rather than average. Are you able to check with the following data and see if your measures still work for you?
Date | WK Start Date | Player | Category | Value |
28/09/2021 | 25/09/2021 | A | 1 | 2000 |
28/09/2021 | 25/09/2021 | B | 1 | 5000 |
28/09/2021 | 25/09/2021 | C | 2 | 6000 |
28/09/2021 | 25/09/2021 | D | 2 | 1500 |
27/09/2021 | 25/09/2021 | A | 1 | 3000 |
27/09/2021 | 25/09/2021 | B | 1 | 4000 |
27/09/2021 | 25/09/2021 | C | 2 | 1000 |
27/09/2021 | 25/09/2021 | D | 2 | 3500 |
23/09/2021 | 18/09/2021 | A | 1 | 4500 |
23/09/2021 | 18/09/2021 | B | 1 | 5000 |
23/09/2021 | 18/09/2021 | C | 2 | 5000 |
23/09/2021 | 18/09/2021 | D | 2 | 9000 |
19/09/2021 | 18/09/2021 | A | 1 | 7000 |
19/09/2021 | 18/09/2021 | B | 1 | 5000 |
19/09/2021 | 18/09/2021 | C | 2 | 2000 |
19/09/2021 | 18/09/2021 | D | 2 | 2500 |
Thankyou!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |