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] ) ) )
)
mcmullenrich's avatar
mcmullenrich
Frequent Visitor
4 years ago

I'm going to try this when I get home. That said, to ensure I understand it and you understand what I'm looking to do, here is how I would solve it in excel:

 

Start with a table having columns "Date" and "Amount"

Add a column called "Weekday" with formula =WEEKDAY(Date)

Off to the right, create a small table with numbers 1 through 7 as rows

Create columns Total (=SUMIF(Weekday, Day, Amount) and Count (=COUNTIF(Weekday, Day)

Divide Total by Count

 

This would give me the total deposits on each weekday and divide it by the number of occurrences of each workday for an average daily total for each weekday. 

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

mcmullenrich 

That is clear enough. But in Power Bi you have a date table with the weekday name column and this table is connected with the fact table through the date column. Is that correct?