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

Thanks tamerj1. I tried this and got the average of all the individual transaction instead of the average daily total for each day. 

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.