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] ) ) )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi mcmullenrich 

Try

DIVIDE (SUM (Table[Value]), CALCULATE (SUM(Table[Value]), ALLSELECTED(Table[Weekday])))

mcmullenrich's avatar
mcmullenrich
Frequent Visitor
4 years ago

Hmm...that got me a 1 for each day. I think I'm using the wrong Table[Value] on one of them:

 

Average per Weekday :=
DIVIDE (
    SUM ( Combined[Amount] ),
    CALCULATE ( SUM ( Combined[Amount] ), ALLSELECTED ( 'Calendar'[Day Of Week] ) ),
    0
)

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

    mcmullenrich 

    I believe I misunderstood your reqyirement. You just want to divide over the number of frequency of the selected weeday. Therefore if you are using a date table just do:

     

    Average per Weekday :=

    DIVIDE (
        SUM ( Combined[Amount] ),
        COUNTROWS ( 'Calendar' )
    )

    • 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?