cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
legrand
Helper I
Helper I

Average per weekday

So I've searched the forums but haven't found a solution yet...

 

I got a table with values which I can of course easily sum by date. I also have a date table in my data model which has a working relationship to the "Timestamp" column. The date table has a WEEKDAY() column, so I can sum by weekday resulting in 106,55€ for Wednesday (02.01.2019 and 09.01.2019) and 54,78€ for Thursday (03.01.2019).

 

What I want to have is the average of the week days which should result in:

  • 53,28€ for Wednesday
  • 54,78€ for Thursday

Everytime I try something with average functions it just gives me the average of all the small values, not aggregated by week day, which I can understand why Power BI does it like that but it is not what I want. A solution with a measure and without creating another SUMMARIZE() table would be greatly appreciated! The resulting averages should be displayed in a column chart if that information is of any help.

 

Can anyone help? Many thanks!

 

TimestampValue
02.01.20197.98 €
02.01.20190.00 €
02.01.201910.01 €
02.01.20198.82 €
02.01.20196.30 €
02.01.2019-3.15 €
02.01.201910.92 €
02.01.2019-5.46 €
03.01.20199.66 €
03.01.20195.04 €
03.01.20195.88 €
03.01.20192.27 €
03.01.20192.27 €
03.01.20192.27 €
02.01.20197.98 €
02.01.20190.00 €
02.01.201910.01 €
02.01.20198.82 €
02.01.20196.30 €
02.01.2019-3.15 €
02.01.201910.92 €
02.01.2019-5.46 €
03.01.20199.66 €
03.01.20195.04 €
03.01.20195.88 €
03.01.20192.27 €
03.01.20192.27 €
03.01.20192.27 €
09.01.20192.94 €
09.01.20192.94 €
09.01.20193.78 €
09.01.20190.00 €
09.01.20190.00 €
09.01.20190.00 €
09.01.201921.01 €
09.01.20195.04 €
09.01.20190.00 €
1 ACCEPTED SOLUTION
legrand
Helper I
Helper I

Ok, so apparently the solution is much simpler than I thought...

 

Average per Weekday = AVERAGEX(Date Table;CALCULATE(SUM('Table'[Value])))

View solution in original post

1 REPLY 1
legrand
Helper I
Helper I

Ok, so apparently the solution is much simpler than I thought...

 

Average per Weekday = AVERAGEX(Date Table;CALCULATE(SUM('Table'[Value])))

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors