Forum Discussion
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
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
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.
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.
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-Power-BI-Turning/ba-p/1187482Power 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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8Thank 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.
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
