Forum Discussion

Quinnie2017's avatar
Quinnie2017
Icon for Helper II rankHelper II
5 years ago
Solved

Calculate weekly average from daily measure?

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!
 
DayWeekStore NumNet SalesCY Store Count
1/1/2021Week 1100$1001
1/2/2021Week 1101$1201
1/3/2021Week 1102$1301
1/4/2021Week 1103$901
1/5/2021Week 1104$1101
1/6/2021Week 1105$1301
1/7/2021Week 1106$1001
     
  Current Result: CY Store Count 7
  Desired Result: Avg Weekly Store Count Per Day 1
     
 
 
Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    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.⚡

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Quinnie2017
      Icon for Helper II rankHelper II
      5 years ago

      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?

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

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