Forum Discussion
Average $ per weekday
I have a table of cash receipts with a date column (i.e., 1/1/2022...4/4/2022). I'd like to calculate the average amount received by week day. So if there were 3 Mondays in the date range and the total collected on those Mondays was $3,000,000, I'd expect to see an output like this:
Weekday | Average
---------------------
Mon $1,000,000
Hi mcmullenrich
Sorry for the late reply. Here is the workable solution as per sample sampleAverage of Amt := DIVIDE ( SUM ( Combined[Amount] ), COUNTROWS ( FILTER ( 'Calendar', NOT ISBLANK ( Combined[Sum of Amt] ) ) ) )
15 Replies
Hi mcmullenrich ,
I'd suggest to create a date dimension in Power BI where one of the attributes is weekday. Here you have a blog post where they explain how to create such a date dimension.
Power Bi for Beginners: How to create a Date Table in Power Bi - SoftcrylicAfterwards, create an Average measure and drag in the WeekDay attribute into your visual.
Let me know if this helps or if yiou have any other questions 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Thanks tackytechtom. So I'm actually working in PowerPivot, not PowerBI. I figured the DAX would be the same, but maybe not. I created a date table and have established the relationship between my transaction table and it. However, the closest I have come to getting the right output is to get the total deposits by weekday divided by the 7 days in the week instead of the 14 distinct occurences of each weekday.
Hi mcmullenrich
you can create a new calculated column for weekday
FORMAT ( WEEKDAY(Table[Date]), "dddd" )
then use it in your visual with simple AVERAGE Measure
Thanks tamerj1. I tried this and got the average of all the individual transaction instead of the average daily total for each day.
Hi mcmullenrich
Try
DIVIDE (SUM (Table[Value]), CALCULATE (SUM(Table[Value]), ALLSELECTED(Table[Weekday])))
