# Calculate Week Average from a measure that calculate daily maximun transactions

• ### Question

• Hoping someone has a number of ideas or a solution.

The requirement I have is to calculate a weekly average from a daily max transaction calculation for each day.
The following measures are working:
MaxTrans:=MAX(TransactionTable[Transactions])
MaxTransOnDay:=MAXX(VALUES(DimDate[Date]),[MaxTrans])
MaxTransOnCurrentWeek:=CALCULATE([MaxTrans],FILTER(All(DimDate), DimDate[WeekOfYearNumber] = MIN(DimDate[WeekOfYearNumber])))

A sample of the transaction table looks like:
User DateTime Transactions
G1 4/25/2017 9:00:00 AM 30
G2 4/25/2017 9:00:00 AM 45
G1 4/25/2017 9:15:00 AM 40
G1 4/25/2017 10:00:00 AM 33
G2 4/26/2017 9:00:00 AM 39
G1 4/26/2017 11:00:00 AM 18

The Pivot looks like: Filtering User, Week, and Day (M-F)
User Date MaxTransOnDay MaxTransOnWeek
G1 4/25/2017 40 40
G1 4/26/2017 18 40

What I am struggling with is the MaxAvgWeek:
User Date MaxTransOnDay MaxTransOnWeek MaxAvgWeek
G1 4/25/2017 40 40 29
G1 4/26/2017 18 40 29

MAxAvgWeek:=CALCULATE(AVERAGEX(Transactions,[MaxTransDay]),FILTER(ALL(Transactions),Transactions[WeekNum]=MAX(Date[WeekNum])))

Thanks!

Thursday, April 27, 2017 10:25 PM

• Hi DRosencrans,

Your result should be as follows, it doesn't return expected result.
User Date MaxTransOnDay MaxTransOnWeek
G1 4/25/2017 40 40
G1 4/25/2017 45 40

For MaxAvgWeek, please try the following formula.

MAxAvgWeek:=SUMX(Transactions,[MaxTransDay])/COUNTROWS(SUMMARIZE(Transactions,Transactions[User]))

Please feel free if you have any other issue.

Best Regards,
Angelia

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Friday, April 28, 2017 9:27 AM