Forum Discussion
Weighted average with dynamic total based on filter
Hello
I am trying to find a weighted average where the average changes based on a date heirarchy filter; The filter is an individual date and start of week.
For example, if I am viewing a bar chart that is at the individual date level, the weighted average for each date should be row 'Count'/Total count for that specific date * Number. If my filter is the "start of week", in this case 1/3/2022, I want the weighted average to be row "Count"/Total count for that specific week (in this case 1/3-1/9) *Number. Basically, I want the denominator to change based on whether I am viewing it at the daily level or the weekly level. Thank you so much in advance!
Anonymous , Create a date table with week columns
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
WeekDay = weekday([Date])These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))Avg of Week =CALCULATE(Averagex(Values(Date[Date]),CALCULATE(sum('Table'[Qty]))) , FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
1 Reply
- amitchandak
Super User
Anonymous , Create a date table with week columns
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
WeekDay = weekday([Date])These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))Avg of Week =CALCULATE(Averagex(Values(Date[Date]),CALCULATE(sum('Table'[Qty]))) , FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s