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
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
    • patri0t82's avatar
      patri0t82
      Post Patron
      5 years ago

      I've JUST ABOUT got the result I need, but it doesn't show up in the visualization properly.

      The code for my column is:

      Average3 =
      CALCULATE(
      SUM('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] = EARLIER('Daily Force Report (Site Resource Forecast)'[Days/Nights])))/5

       

       

      The correct numbers of 15.4 (Nights) and 135.4 (Days) are showing up for Week 6, HOWEVER, when I add this to the visualization, the numbers appear SUM into the thousands. I just need 15.4, 135.4 and 150.8 to show up for each day of that week. Not the sum of all of them.

       

      Can somebody please lend their expertise. Thank you so much.