Average per day with multiple values each day

[Anonymous]

Hi,
I am doing a report where I would like to get the average for each day. 

The issue I am facing is that there is multiple values on each day which is making the average much lower than expected. 

The table contains one row per pause. First of all it needs to be sum of duration per day since it is multiple values each day.
Then I need to get an average per week (existing relatonship with a date table). When I am doing the average right now I am getting a very low average since it is calculating the average per row and not sum per day per membername.

[v-rongtiep-msft · Accepted solution]

Hi @Anonymous ,

Please refer to my pbix to see if it helps you.

The relationship between the tables.

Then create a measure.

Measure = CALCULATE(AVERAGE('Table'[DURATION_Seconds]),FILTER(ALL('Table'),'Table'[date]=SELECTEDVALUE('Table 2'[Date])))

 Could you tell that you want to calculate Average per day or Average per week?

If I have misunderstood your meaning, please provide your desired output and your pbix file without privacy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , A measure like this can do Sum on day level and Avg post that

averageX(values('Date'[Date]), calculate(Sum(Table[Duration])))

You can use this measure in WOW formula

refer if needed

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8