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 

you can create a new calculated column for weekday 

FORMAT ( WEEKDAY(Table[Date]), "dddd" )

then use it in your visual with simple AVERAGE Measure

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' )
        )