Forum Discussion

icturion's avatar
icturion
Resolver II
5 years ago
Solved

Calculate average value per weekday

Hi   The aim is to calculate the average value per day of the week.   Table: I only get the average per record and not the average per day of the week.    Maybe someone can help
Need Help
  • icturion's avatar
    icturion
    5 years ago

    Hi,

     

    I revolved it:

     

    Measure 1  = SUM(Table [aantal monsters])

    measure 2 = AverageX ( VALUES(table[DATE]), [measure 1])

     

    thx 

amitchandak's avatar
amitchandak
Super User
5 years ago

icturion , have date table, There you define a week and week rank as column

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

 

And use a measure like this
This Week = CALCULATE(AverageX(values(Date[Date]),Sum(Table[Antal monsters])), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

 

refer

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

icturion's avatar
icturion
Resolver II
5 years ago

I'm probably doing something wrong, because the results are wrong. Suppose on Sundays in September I took a total of 20 samples. so far September has had 2 Sundays. then the average should be 10 samples per Sunday.