cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
patri0t82
Resolver I
Resolver I

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 [Value] from Feb 1-7. For every [Date] record that contains Feb 5, it would also show the average of all [Value] Feb 1-7. Any [Date] record from Feb 8 thru 14 will show the average [Value] sum from Feb 8 thru 14.

9 REPLIES 9
v-jayw-msft
Community Support
Community Support

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.

6.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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:

 

Screenshot 2021-02-26 075609.jpg

 

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.

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.

 

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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

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

 

Correct.png

 

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.

patri0t82
Resolver I
Resolver I

Hello,

I've attempted to use the information above with limited success. I've added an Excel workbook which provides the two datasets I'm attempting to combine and a picture to show current status.

 

The dark blue represents Site Resource Forecast whether it be Day/Night or Total

The green represents Force Reporting totals from Monday - Friday, filterable on Day/Night

What I'm looking for is a third measure that will tell me the average total of the green bars in a straight line, week by week.

In the image below, for example, from 2/1/2021 - 2/8/2021, the new bar or line will show 150.8 (or 151) for every day of that week. When I click on Days or Nights (as seen in the second image), the average for the first week should show 15.4 (or 15) for every day of that week.

 

https://drive.google.com/file/d/1LTPmcvz1eVWEZ4NIfjqcl4x0AASouSQu/view?usp=sharing 

 

Screenshot 2021-02-24 141348.jpg

Screenshot 2021-02-24 142710.jpg

patri0t82
Resolver I
Resolver I

Thank  you for your response.

I have added a column to my table CalendarTable:

Week Start date(Mon-Sun) = CalendarTable[Date]+-1*WEEKDAY(CalendarTable[Date],2)+1

The column appears to show the ideal outcome.

With that said, when I create my measure:
Measure = AVERAGEX(Values(CalendarTable[Week Start date(Mon-Sun)]), CALCULATE(SUM('Daily Force Report'[Total - Total_])))
and use it in a visualization, it returns the same values as 'Daily Force Report'[Total - Total_)

Can you tell if I'm doing something wrong? As far as relationships, I currently have CalendarTable[Date] pointing one way toward 'Daily Force Report'[Date]. I tried setting it to both, but it made no difference.

amitchandak
Super User
Super User

@patri0t82 , create a week from Monday to Sunday. refer my blog

 

and have a measure like

 

averagex(Values(Date[Date]), calculate(sum(Table[Value])))

 

Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors