Measure - how to sum value over a week (for players) and then average this based on category

[LS-PScience]

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

[colacan · Accepted solution]

@LS-PScience Hi LS-PScience,

Please try belwo two measures.

SumByWkByPlayer =

CALCULATE(sum(sumvalueweek[Value]),

    ALLEXCEPT(sumvalueweek,sumvalueweek[WK Start Date],sumvalueweek[Player])

)

CategoryAvg =

CALCULATE(

   AVERAGEX(

        ADDCOLUMNS(

            SUMMARIZE( sumvalueweek, sumvalueweek[Player], sumvalueweek[WK Start Date] ),

           "sumvalue", CALCULATE( SUM( sumvalueweek[Value] ) )

            ),

           [sumvalue]

        ),

  REMOVEFILTERS( sumvalueweek[Date] )

)

Hope this helps you.

Please mark it as the solution if it helped you.

[VahidDM]

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🙏!!

[LS-PScience]

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!