Forum Discussion

jimjawn's avatar
jimjawn
New Member
7 years ago

Get an average based on the Week Day

 

I'm trying to figure out what the average number of calls received by day of week is.  If you look at the chart below, I'm able to count the total call volume by day of year.  In the chart on the bottom, I'm able to calculate the total call volume based on the day of the week.

 

However, you'll see that are more Wednesdays, Thursdays & Fridays in the data so I need to get an average number of calls by day of week.  

 

I don't know what I'm missing but I cannot figure this out.  Willing to trade a first born.  Thanks All!

Power BI

Need Help

1 Reply

  • PANDAmonium's avatar
    PANDAmonium
    Resolver IV
    7 years ago

    You need to get Day of Week (which is looks like you already have)

    = Table.AddColumn(#"Added Custom", "Day", each Date.DayOfWeek([Date]))

    As well as the week of year

    = Table.AddColumn(#"Changed Type1", "WeekOfYear", each Date.WeekOfYear([Date]))

     

    Group By on Week of Year and Day of Week while aggregating for Count of Calls.

    Group By again on Day while aggregating for Average of Count

     

    You might want to also look into creating a sort table to logically order your Days of Week

    https://community.powerbi.com/t5/Desktop/Slicer-Ordering/m-p/772175#M372058