Calculate Average of Maximum Transactions Per Day by Week

[drosencrans]

Hello Power BI Community,

My scenerio:  calculate the weekly average based on the maximum # transactions per datetime.

I have the following measures working:

MaxTrans:=MAX(TransactionTable[Transactions])

MaxTransOnDay:=MAXX(VALUES(DimDate[Date]),[MaxTrans])

MaxTransOnCurrentWeek:=CALCULATE([MaxTrans],FILTER(All(DimDate), DimDate[WeekOfYearNumber] = MIN(DimDate[WeekOfYearNumber])))

AvgOfMaxTransDay:=?

Data looks like:

	MaxTransOnDay	MaxTransOnWeek	AvgOfMaxTransDay
Rover
Week 15
4/14/2017	70	75	51
4/13/2017	75	75	51
4/12/2017	50	75	51
4/11/2017	30	75	51
4/10/2017	30	75	51
Week 14
4/7/2017	90	90	46
4/6/2017	40	90	46
4/5/2017	23	90	46
4/4/2017	33	90	46
4/3/2017	44	90	46

Thanks for any direction/assistance!

Doug

[v-qiuyu-msft]

Hi @drosencrans,

You can build a relationship between the Date table and fact table use date column field, then create a measure like below:

AvgOfMaxTransDay = CALCULATE(AVERAGE(Table2[Transactions]),FILTER(ALL(Table2),'Table2'[Weeknum]=MAX('Date'[WeekNum])))

Best Regards,
Qiuyun Yu

[drosencrans]

Hello Qiuyun Yu,
 
Thanks for the assist.  My data and model are at a different granularity.  
 
I can have multiple date (datetime) transansactions. Need 3 measures
1. MaxTransDay = MAX(Transactions[Transactions])
2. MaxTransWeek = CALCULATE(MAX(Transactions[Transactions]),FILTER(ALL(Transactions),'Transactions'[Weeknum]=MAX('Date'[WeekNum])))
3. AvgMaxWeek = CALCULATE(AVERAGEX(Transactions,[MaxTransDay]),FILTER(ALL(Transactions),Transactions[WeekNum]=MAX('Date'[WeekNum])))

AvgMaxWeek is calculating the average over the entire week and not the MaxTransDay.  Need the Average of the MaxTransDay.

URL for sample: 

https://1drv.ms/u/s!AjYWvmK-s42rawdKD2MUjezVUsw

Thanks again for the additonal support!
Doug

[v-qiuyu-msft]

Hi @drosencrans,

The URL you shared is not accessible. Would you please sample about the requirement "AvgMaxWeek is calculating the average over the entire week and not the MaxTransDay.  Need the Average of the MaxTransDay."?

Best Regards,
Qiuyun Yu

[drosencrans]

Odd - OneDrive blocked the account.  Support said that this should be fixed tomorrow.

In the meantime, I am able to calculate the Max (top) transaction for the day.  Each day has multiple transactions per hour.  I calculate the Max for that day.  I also calculate the Max for the week.  I also have a need to calculate the Average for the week based on each day's Max.  I do a number of other calculations for which day, hour had the max. 

Just need to figure-out how to get the average from the Max Each Day.

Example:

For week 1:

Day 1 Max = 55

Day 2 Max = 44

Day 3 Max = 55

Day 4 Max = 70

Day 5 Max = 30

{I exclude/filter out Sat and Sun, Day 6 and 7}

Average for the Week = 51

Thanks,

Doug

[v-qiuyu-msft · Accepted solution]

Hi @drosencrans,

You can create a calculated table to return maximum transactions per day in one column,

Table = SUMMARIZE('Table2','Table2'[Date],'Table2'[Weeknum],"MaxPerDay",MAX('Table2'[Transactions]))

Build a relationship between this new table with Date table, then create a measure in this new table like this:

Measure = CALCULATE(AVERAGE('Table'[MaxPerDay]),FILTER(ALL('Table'),'Table'[Weeknum]=MAX('Date'[WeekNum])))

Please download the attached .pbix to see if the issue meet your requirements.

Best Regards,
Qiuyun Yu

[drosencrans]

Hello Qiuyun Yu,

This is an interesting solution that works for Power BI Desktop.  Is there a comparable for PowerPivot using DAX as I often model in both?

Thanks,

Doug

[v-qiuyu-msft]

Hi @drosencrans,

As both Power BI desktop and PowerPivot support DAX, the solution I shared should also work in PowerPivot. If you have any question regarding PowerPivot, you can pot a thread here to get dedicated support.

From your description, it seems the original issue is solved, would you please mark a helpful reply as an answer so that we can close the thread?

Best Regards,
Qiuyun Yu

[drosencrans]

Hey All!

I realized that I have been caclulting my Average correctly:

WeeklyAvg:=CALCULATE([AverageXOfMP],FILTER(DimDate, DimDate[WeekNumberofYear]))

The problem I am having are wil the filters being applied as part of the pivot are not adhered to when I exclude Sat and Sun.  I need to change the filter context, but not sure why the day filter is being removed.

Any idea where I should look?

Thanks,

Doug