Forum Discussion

patri0t82's avatar
patri0t82
Post Patron
5 years ago

Average for week

Hello, I am trying to create a column that will find the average weekly sum, from Monday thru Sunday.   For example, for every [Date] record that contains Feb 1, it would show the average of all [...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi patri0t82 ,

 

You will need to create a week column on Daily Force Report.

week = WEEKNUM('Daily Force Report'[Date],2)

Then create a measure or column using ALLEXCEPT() function as below.

Column = CALCULATE(SUM('Daily Force Report'[Total - Total_]),ALLEXCEPT('Daily Force Report','Daily Force Report'[week]))/CALCULATE(DISTINCTCOUNT('Daily Force Report'[Date]),ALLEXCEPT('Daily Force Report','Daily Force Report'[week]))

 Result would be shown as below.

 

Best Regards,

Jay

  • patri0t82's avatar
    patri0t82
    Post Patron
    5 years ago

    Thank you for your support. When I use the columns you've created (in the visualization you'll see I've called your "column" "average", it appears like this in my chart:

     

     

    Unfortunately it's not quite what I was hoping for. Though the light blue bars are straight across showing the average for the week, the number shown is incorrect. The average for the first week should be 150.8 (754 / 5). Furthermore, it still remains unaffected by selecting Days/Nights.

    • patri0t82's avatar
      patri0t82
      Post Patron
      5 years ago

      I have been able to to adjust my source to only include weekdays, so the average per week is showing at 150 for the first week, as desired. The only other problem with this calculated column:

      Average =
      CALCULATE(SUM('Daily Force Report (Site Resource Forecast)'[Total - Total_]),
      ALLEXCEPT('Daily Force Report (Site Resource Forecast)','Daily Force Report (Site Resource Forecast)'[Week]))
      /
      CALCULATE(DISTINCTCOUNT('Daily Force Report (Site Resource Forecast)'[Date]),
      ALLEXCEPT('Daily Force Report (Site Resource Forecast)','Daily Force Report (Site Resource Forecast)'[Week]))
       
      is that it doesn't respond to Days/Nights, which is a must have, if possible. Any help is greatly appreciated.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi patri0t82 ,

         

        If you want the value be affacted by days/nights slicer, you will need to add FILTER() function in the formula, like:

        calculate(avg(xxx),filter(allexcept(table,column),days/nights=selectedvalue(days/night)))

         

        Best Regards,

        Jay