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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]User[/TD]
[TD]DateTime[/TD]
[TD]Transactions[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD]4/25/2017 9:00:00 AM[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]G2[/TD]
[TD]4/25/2017 9:00:00 AM[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD]4/25/2017 9:15:00 AM[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD]4/25/2017 10:00:00 AM[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]G2[/TD]
[TD]4/26/2017 9:00:00 AM[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD]4/26/2017 11:00:00 AM[/TD]
[TD]18[/TD]
[/TR]
</tbody>[/TABLE]

The Pivot looks like: Filtering User, Week, and Day (M-F)
[TABLE="width: 500"]
<tbody>[TR]
[TD]User[/TD]
[TD]Date[/TD]
[TD]MaxTransOnDay[/TD]
[TD]MaxTransOnWeek[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD]4/25/2017[/TD]
[TD]40[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD]4/26/2017[/TD]
[TD]18[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]

What I am struggling with is the MaxAvgWeek:
[TABLE="width: 500"]
<tbody>[TR]
[TD]User[/TD]
[TD]Date[/TD]
[TD]MaxTransOnDay[/TD]
[TD]MaxTransOnWeek[/TD]
[TD]MaxAvgWeek[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD]4/25/2017[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD]4/26/2017[/TD]
[TD]18[/TD]
[TD]40[/TD]
[TD]29[/TD]
[/TR]
</tbody>[/TABLE]

[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:
[TABLE="width: 605"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Location[/TD]
[TD]WeekNum[/TD]
[TD]Date[/TD]
[TD]MaxDay[/TD]
[TD]AvgCurrentWeek[/TD]
[TD]AvgPrevWeek[/TD]
[/TR]
[TR]
[TD]Cust1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]4/3/2017[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]4/6/2017[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust1[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]4/10/2017[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD]Cust69[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]4/11/2017[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rover[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]4/10/2017[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]73[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rover[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]4/11/2017[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]73[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Power BI Table with filters/measures:
[TABLE="width: 283"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Location[/TD]
[TD]WeekNum[/TD]
[TD]MaxDay[/TD]
[TD]MAxAvgWeek[/TD]
[/TR]
[TR]
[TD]Cust1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD]Cust1[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]73[/TD]
[/TR]
[TR]
[TD]Cust69[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD]Cust69[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]73[/TD]
[/TR]
[TR]
[TD]Rover[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD]Rover[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]73[/TD]
[/TR]
</tbody>[/TABLE]

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