January 12, 2018 at 8:20 pm #8883
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.
Can someone provide much needed insights?
Thanks!January 12, 2018 at 8:46 pm #8884
Sorry – file size was too large.
Attachments:You must be logged in to view attached files.January 12, 2018 at 8:47 pm #8886
…and the sample spreadsheet/pivot data.
Attachments:You must be logged in to view attached files.January 23, 2018 at 3:51 pm #8964
I was able to modify the summarized table to get the correct max percent and then calculate the average for the week and month.
I’ll submit a different question and hope to get a response on creating a the calculation for the time at max percent and then determining the average time for the week and month based on those max percents.
You must be logged in to reply to this topic.