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
        )