Calculate weekly average from daily measure?

[Quinnie2017]

I'm having trouble with a measure that works well on a daily level - but sums at a weekly level - and I would prefer to see average at a weekly level.

This measure works good for daily:

CY Sales Store Count = CALCULATE(COUNT(NetSales_And_Traffic[Net Sales]),FILTER(NetSales_And_Traffic,NetSales_And_Traffic[Net Sales]>0))

And the measure works for weekly but sums the store counts.

I need a measure that averages the daily store counts for the week.

Something like: Avg Weekly Store Count Per Day = AVERAGE([CY Sales Store Count]), but that is not an option because you can't average a measure?

Sample data below with current result and desired result.

Any help in the right direction would be appreciated. Thank you!

Day	Week	Store Num	Net Sales	CY Store Count
1/1/2021	Week 1	100	$100	1
1/2/2021	Week 1	101	$120	1
1/3/2021	Week 1	102	$130	1
1/4/2021	Week 1	103	$90	1
1/5/2021	Week 1	104	$110	1
1/6/2021	Week 1	105	$130	1
1/7/2021	Week 1	106	$100	1
		Current Result: CY Store Count		7
		Desired Result: Avg Weekly Store Count Per Day		1

[parry2k]

@Quinnie2017 correct, values give you a table of unique values of the column you have in the values function.

[parry2k · Accepted solution]

@Quinnie2017 use AVERAGEX

Avg Weekly = AVERAGEX( VALUES (Table[Date] ), [CY Sales Store Count])

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Quinnie2017]

This works - thanks!

To make sure I understand what DAX is doing:

It is looking at the number of values in [Date], which in this case is 7 because it is one week, and making that 7 the denominator.  And the numerator is [CY Sales Store Count].  The end result it the average of [CY Sales Store Count] / 7.

Is that right?