DAX - Calculate Average Of Max Day Transactions For Week

[dlrosencrans]

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

<tbody>
</tbody>

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

<tbody>
</tbody>

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

<tbody>
</tbody>

[FONT=&quot]MAxAvgWeek:=CALCULATE(AVERAGEX(Transactions,[MaxTransDay]),FIL[/FONT]<wbr style="box-sizing: inherit; color: rgb(0, 0, 0); font-family: "Segoe UI", "Segoe WP", Tahoma, Arial, sans-serif; font-size: medium;">[FONT=&quot]TER(ALL(Transactions),Transactions[WeekNum]=MAX(D[/FONT]<wbr style="box-sizing: inherit; color: rgb(0, 0, 0); font-family: "Segoe UI", "Segoe WP", Tahoma, Arial, sans-serif; font-size: medium;">[FONT=&quot]ate[WeekNum])))[/FONT]

Thanks!

 

[gazpage]

I don't think I understand some of what you have done with your existing measures. Do MaxTrans and MaxTransOnDay return different values?

Anyway, if I understand correctly what you are trying to do, ie report the average daily maximum for the week that you are looking at. If so you want to iterate over the column that lists the days.

MAxAvgWeek:=CALCULATE(AVERAGEX(VALUES(Date[Days]),[MaxTransDay]),FIL<wbr style="background-color: rgb(250, 250, 250); box-sizing: inherit; color: rgb(0, 0, 0); font-family: "Segoe UI", "Segoe WP", Tahoma, Arial, sans-serif; font-size: medium;">TER(ALL(Transactions),Transactions[WeekNum]=MAX(D<wbr style="background-color: rgb(250, 250, 250); box-sizing: inherit; color: rgb(0, 0, 0); font-family: "Segoe UI", "Segoe WP", Tahoma, Arial, sans-serif; font-size: medium;">ate[WeekNum])))

 

[dlrosencrans]

Thanks for the response. Has helped, but I have a feeling my filter contexts are off. Back to training/review.

Here is what I am ultimately after:

Pivot Table:

Location	WeekNum	Date	MaxDay	AvgCurrentWeek	AvgPrevWeek
Cust1	15	4/3/2017	33	39
Cust1	15	4/6/2017	45	39
Cust1	16	4/10/2017	22	22	39
Cust69	16	4/11/2017	50	50
Rover	16	4/10/2017	69	73
Rover	16	4/11/2017	77	73

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Power BI Table with filters/measures:

Location	WeekNum	MaxDay	MAxAvgWeek
Cust1	15	45	39
Cust1	16	22	73
Cust69	15		39
Cust69	16	50	73
Rover	15		39
Rover	16	77	73

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Cust1 weeknum 16 avg should be 22
Cust69 should not have an avg for week 15 no data, and week 16 avg should be 50 not 73
Rover avg for week 16 is correct, but should have no data for week 15.

Table = SUMMARIZE('Transaction','Transaction'[Location],'Transaction'[MonthNumber],'Transaction'[Date],'Transaction'[WeekNum],"MaxPerDay",MAX('Transaction'[Transactions]))
MaxDay = MAX('Table'[MaxPerDay])
MAxAvgWeek = CALCULATE(AVERAGEX(VALUES('Date'[Weekday]),[MaxDay]),FILTER(ALL('Table'),'Table'[WeekNum]=MAX('Date'[WeekNum])))

Thanks!
Doug

 

[gazpage]

Sorry, if I didn't help. Not sure I was / am 100% understanding.

Looking again at the formula, why is are using WeekNum from two different tables? What happens if you take both from the Date table?

 

[dlrosencrans]

I have a Date table that is joined to the Table. Date is then filtered by year, month, and then date. WeekNum is in both tables. Based on the date selected in the filter among other filters, I then am attempting to apply the measure against those filters.

Seems there is an error when I try to replace Table[WeekNum] with Date[WeekNum], because this is not a single value.

I'm missing something and will consult more documentation/samples.

Thanks,
Doug

 

[gazpage]

Actually, I can see a problem in the code. You are removing the filtering on 'Table' by calling it with ALL(). Maybe try something like this.

MAxAvgWeek = CALCULATE(AVERAGEX(VALUES('Date'[Weekday]),[MaxDay]),FILTER(ALL('Date'),'Date'[WeekNum]=MAX('Date'[WeekNum])))

Or otherwise maybe use All('Table'[WeekNum]) so you are only removing the filtering from that column.

 

[dlrosencrans]

Thanks for the additional assist.

I am going to go back and review filter contexts as this appears to be the root of my confusion.

Doug