Forum Discussion

mcmullenrich's avatar
mcmullenrich
Frequent Visitor
4 years ago
Solved

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 tot...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi mcmullenrich 
    Sorry for the late reply. Here is the workable solution as per sample sample

    Average of Amt :=
DIVIDE (
    SUM ( Combined[Amount] ),
    COUNTROWS ( FILTER ( 'Calendar', NOT ISBLANK ( Combined[Sum of Amt] ) ) )
)
tackytechtom's avatar
tackytechtom
Most Valuable Professional
4 years ago

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 - Softcrylic

 

Afterwards, 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/

mcmullenrich's avatar
mcmullenrich
Frequent Visitor
4 years ago

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.