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

      • patri0t82's avatar
        patri0t82
        Post Patron
        5 years ago

        Thank you for the response - I've transformed your code into this below, though it returns blank values.

         

        Average =
        CALCULATE(
        AVERAGE('Daily Force Report (Site Resource Forecast)'[Total - Total_]),
        FILTER(
        ALLEXCEPT('Daily Force Report (Site Resource Forecast)','Daily Force Report (Site Resource Forecast)'[Week]),[Days/Nights] =
        SELECTEDVALUE('Daily Force Report (Site Resource Forecast)'[Days/Nights])))
         
        At the very least there are no error messages. Thank you for your continued help. Hopefully we're really close.
        To be clear, for each row that shows Days in the [Days/Nights] column Week 6 should show 135.4. For each Nights for week 6 it should be 15.4, the total average for week 6 should be 150.8