How can we sum individual daily totals and then average those numbers?
Requirement
We want the average of all daily total sales.
A single date could have multiple entries in table TblSales. I want to sum individual daily totals and then average those numbers to get a single value

Files: Pbix & Excel
Final Measure
Average of Daily Totals = AVERAGEX(VALUES('Date'[Date]), [Total Sales])
Setup
Raw Data
Table TblSales has 31 rows. Each row is a sale.

I created a date table called TblDate using M code. See full code at bottom.
I created a relationship between tables TblSales and TblDate.

Solution
Base Measure
I created [Total Sales] measure. I’ll use it inside the final measure.
Total Sales = SUM(TblSales[Sales])
Used by itself, measure [Total Sales] gives us $39633.00 but I need to split this amount out for each day that has sales.
Final Measure
It’s called [Average of Daily Totals] and gives us a single answer of $4403.67
Average of Daily Totals = AVERAGEX(VALUES('TblDate'[Date]), [Total Sales])

Solution Explanation
Averagex Syntax
AVERAGEX( <table>, <expression>)
The table is: VALUES(‘TblDate'[Date]). The expression is: [Total Sales].
What does VALUES do?
VALUES function creates a table with a single column of dates. [Total Sales] expression is calculated for each date.
Invisible measure values
It’s bad practice to use DAX to create physical tables just to see virtual data inside a measure. I’ll do it for demo purposes.
On the ribbon click ‘Modeling’, ‘New Table’ and enter the DAX below.
zInsideMeasureAverageofDailyTotals = ADDCOLUMNS(VALUES('TblDate'[Date]), "Daily Sum", [Total Sales])

I see each Date and it’s [Total Sales] value (also days with no sales). This is a table (physical data). The beauty is that measure [Average of Daily Totals] creates these values internally!
Final Step: AVERAGEX
Now that we can physically see what’s inside function AVERAGEX it’s easier to understand. Final step is the average of [Total Sales] values.
BONUS: see Daily Totals & Avg
I can also display daily totals in a visual like this:

How would we average these numbers in the total row?
It’s possible using this measure:
Total Daily Sales with Average (not Total) = if( NOT(HASONEVALUE(TblDate[Date])), AVERAGEX(VALUES('TblDate'[Date]), [Total Sales]), SUM(TblSales[Sales]))
I get the same row values but total row is the average of numbers above!

How? The total row does not have a date. So, functions NOT and HASONEVALUE check for this and let us replace the default function for the total row (SUM) with something different. In this case it’s:
AVERAGEX(VALUES('TblDate'[Date]), [Total Sales]),
fancy code that gives us the average of the daily sums!
Date Table M Code
M code I used in Power Query to create table Date. Note: no dynamic start and end dates. It starts on Oct 1 2020 and has 31 dates. In a future post I’ll explore dynamic date tables.
let Source = List.Dates(#date(2020, 10, 1), 31, #duration(1, 0, 0, 0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}), #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type), #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text), #"Inserted First Characters" = Table.AddColumn(#"Inserted Month Name", "Month Short", each Text.Start([Month Name], 3), type text), #"Inserted Week Number" = Table.AddColumn(#"Inserted First Characters", "Week Number", each Date.WeekOfYear([Date])), #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Week Number",{{"Week Number", Int64.Type}}), #"Inserted Day Name" = Table.AddColumn(#"Changed Type1", "Day Name", each Date.DayOfWeekName([Date]), type text), #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek(([Date]), Day.Sunday)+1, Int64.Type), #"Inserted First Characters1" = Table.AddColumn(#"Inserted Day of Week", "Day Short", each Text.Start([Day Name], 3), type text), #"Reordered Columns" = Table.ReorderColumns(#"Inserted First Characters1",{"Date", "Year", "Month Name", "Month Short", "Day Name", "Day Short", "Day of Week"}) in #"Reordered Columns"
DAX Function Links
- AVERAGEX https://dax.guide/averagex/
- VALUES https://dax.guide/values/
- ADDCOLUMNS https://dax.guide/addcolumns/