Forum Discussion

Paul_Cracknell's avatar
Paul_Cracknell
Copper Contributor
Jul 06, 2022

Avg Sales Per Week Day using DAX

Hi, all. I have a dimension table that is a standard date dimension table - has all the usual elements (date, day of week, week number, month number, etc).

 

I also have a fact table with sales for each date.

 

I am interested in getting the average sales per day of the week. For example, average sold on Monday. Average sold on Tuesday. Etc.

 

I am using: AVERAGEX(VALUES(dCalendar[Day of Week]),[QTY]) but it's producing numbers that don't square with reality.

 

Any other suggestions?

 

Paul

excel
power bi

3 Replies

Sort By
  • Paul_Cracknell's avatar
    Paul_Cracknell
    Copper Contributor
    Jul 06, 2022
    @Sergei, Thank you. This is giving me the average of each order on those week days. I am looking for the average of the total quantity for each of those weekdays. That's why I tried AVERAGEX because it would sum the quantity for each weekday first, then run the average for each weekday.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 07, 2022

      Paul_Cracknell 

      If I understood your logic correctly that could be

      Total Qty:=SUM( Table1[QTY] )
Another Average:=AVERAGEX( 'Calendar', Table1[Total Qty] )

      Another way is

      Distinct Dates:=DISTINCTCOUNT( Table1[Date] )
Same Average:=DIVIDE( [Total Qty], [Distinct Dates], 0 )

Resources