cancel
Showing results for
Did you mean:
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!

 Timestamp Value 02.01.2019 7.98 € 02.01.2019 0.00 € 02.01.2019 10.01 € 02.01.2019 8.82 € 02.01.2019 6.30 € 02.01.2019 -3.15 € 02.01.2019 10.92 € 02.01.2019 -5.46 € 03.01.2019 9.66 € 03.01.2019 5.04 € 03.01.2019 5.88 € 03.01.2019 2.27 € 03.01.2019 2.27 € 03.01.2019 2.27 € 02.01.2019 7.98 € 02.01.2019 0.00 € 02.01.2019 10.01 € 02.01.2019 8.82 € 02.01.2019 6.30 € 02.01.2019 -3.15 € 02.01.2019 10.92 € 02.01.2019 -5.46 € 03.01.2019 9.66 € 03.01.2019 5.04 € 03.01.2019 5.88 € 03.01.2019 2.27 € 03.01.2019 2.27 € 03.01.2019 2.27 € 09.01.2019 2.94 € 09.01.2019 2.94 € 09.01.2019 3.78 € 09.01.2019 0.00 € 09.01.2019 0.00 € 09.01.2019 0.00 € 09.01.2019 21.01 € 09.01.2019 5.04 € 09.01.2019 0.00 €
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I

## Re: Average per weekday

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

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

## Re: Average per weekday

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

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021