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

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

Yes, that's 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] ) ) )
)
  • 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 )