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 ,

 

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.