I hopefull someone can point me in the right direction or provide a solution as I am hitting a wall.
I’ve created a summarized table, PercentOccupiedTable that has summarizes across location, rawdate, weeknumber, monthnumber, timestamp that summarizes MAX percent occupied and Min peak time for a set of rolling transactions.
I ahve the daily metrics working just fine and all averages (I have another summarized table RollingMTTable for the rolling totals), except for max avg. % for the week and eventually the avg min time of the peak total.
Need to determine the Avg Percent of the Max Daily Percentage for the week.
Current formula does not return the correct result:
AvgPercentCurrentWeek = CALCULATE(AVERAGE(‘PercentOccupiedTable'[MaxPercentDay]),FILTER(ALL(DimDate),DimDate[YearNumber]=MAX(DimDate[YearNumber])&&DimDate[WeekdayShortName]<>”Sat”&&DimDate[WeekdayShortName]<>”Sun”&&DimDate[WeekNumber]=MAX(DimDate[WeekNumber])))
I am sure I am missing either a calculate or filter….
In the attached sample dimensions and facttable data (spreadsheet and powerbi).
The pivot has the sample results.
The PowerBI has the summarized tables and results. The Avg Percent For the week is taking the entire dataset for the average and not the max percent for each day in the week.