Jul 06 2022 12:20 PM
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
Jul 06 2022 12:55 PM
It's not clear how you'd like to use it. If the model is like
you may add measure
Average Qty:=AVERAGE( Table1[QTY] )
and show it as
Jul 06 2022 01:41 PM
Jul 07 2022 09:16 AM
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 )