Home Forums Power BI Calculating Weekly Avg % Based On Daily Max %

This topic contains 3 replies, has 1 voice, and was last updated by  drosencrans 10 months, 3 weeks ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #8883

    drosencrans
    Participant
    • Started: 3
    • Replies: 8
    • Total: 11

    Hi All,

    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!

    #8884

    drosencrans
    Participant
    • Started: 3
    • Replies: 8
    • Total: 11

    Sorry – file size was too large.

    Attachments:
    You must be logged in to view attached files.
    #8886

    drosencrans
    Participant
    • Started: 3
    • Replies: 8
    • Total: 11

    …and the sample spreadsheet/pivot data.

    Attachments:
    You must be logged in to view attached files.
    #8964

    drosencrans
    Participant
    • Started: 3
    • Replies: 8
    • Total: 11

    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.

    Thanks!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.