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?

  • mcmullenrich's avatar
    mcmullenrich
    Frequent Visitor
    4 years ago

    Not quite there yet. I realized the issue, just not sure how to solve in DAX. My excel-based approach above only works if I have a single entry for each date (i.e., a daily deposit amount). However, in my PBI table, I have multiple transactions for each date. As such, your approach gives me much smaller numbers than it should be. I need to find a way to sum by date before adding and counting.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    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

    Yes, that's correct

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

    Then I hope the last solution works. 

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

    mcmullenrich 
    Even though, COUNTROWS ( 'Calendar' ) should give the frequency count of the selected weekday as the date table has no duplicates. Can you please share some screenshots of you tabels, data model, report and the measure?

  • mcmullenrich's avatar
    mcmullenrich
    Frequent Visitor
    4 years ago

    This feels a bit like I cheated, but it did get me the result that I wanted:

    Average per Weekday :=
    DIVIDE ( [Sum of Amt], MAX ( Combined[WeekNo] ) - MIN ( Combined[WeekNo] ) + 1 )