Forum Discussion

t-dahen's avatar
t-dahen
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

Categorical Rolling Average

Hello,

 

I have a table of the form:

 

Name | Category | Date | Sales

 

-It should be noted that there is a N:1 relationship between a Name and a Category (The same Name will have the same Category, but multiple Name's can share the same Category)

-There can be duplicate Dates for the same Name.

NameCategoryDateSales
BobA6/19/202010
AliceB6/19/202013
BobA6/19/202012
JoeB6/19/202015
BobA6/20/202014

 

Is there a way to:

  1. Get the rolling 7 day average of Sales for each Name (let's call it NameRollAvg)
  2. Now for each Category, get the average of SalesRollAvg for all Names belonging to that category (let's call it CategoryRollAvg). However, I do not want simply the average of sales for each category.

 

Ultimately I would like to use these two numbers to compare a Name to the other Name's in its Category:

 

 

 

This is not representing the sample data above.

 

 

  • stevedep's avatar
    stevedep
    6 years ago

    t-dahen ,
    ah missed that summarization of the sales per day needs to be done first. Adjusted! output is now as expected:

    Adjusted code:

     

    _2_Measure_RunningAverageNameCategorySales = 
var _CTfilter = DATESINPERIOD('Date'[Date]; MIN('Date'[Date]);-7;DAY)
var _CT = CALCULATETABLE(SUMMARIZE('Table';'Table'[Name];'Date'[Date];"sales"; CALCULATE(SUM('Table'[Sales]))) ;_CTfilter;ALLEXCEPT('Table';'Table'[Name];'Table'[Category]))
return
IF(NOT(ISBLANK(MIN('Table'[Sales])));
AVERAGEX(_CT;[sales]);
BLANK())
    _1_Measure_AverageOfAverageRunningSalesinCat = 
var _sumtbl = CALCULATETABLE(SUMMARIZE('Table';'Table'[Name];'Date'[Date];'Table'[Category]; "runningsales"; [_2_Measure_RunningAverageNameCategorySales]);ALLEXCEPT('Table';'Date';'Table'[Category]))
return
    AVERAGEX(_sumtbl; [runningsales])

     

    New file available here. 

    Hope we got it now?

    Kind regards, Steve. 

18 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago
    NameRollAvg =
var n = SELECTEDVALUE('Table'[Name])
var d = SELECTEDVALUE('Table'[Date])
return calculate (
avg('Table'[Sales])
,'Table'[Date] >= d-7
,'Table'[Name] = n
)

     

    "However, I do not want simply the average of sales for each category."

     

    Why not? That would be the most "fair"  calculation.

    • t-dahen's avatar
      t-dahen
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      Thank you for the response,

       

      I want to compare these two numbers NameRollAvg and CategoryRollAvg. Basically to see how did this Name compare to others in it's Category. Take for example, Bob's Sales for this week is 1 and Alice's Sales for this week is 14 and they are both Category A. If we did "average of sales per category" the CategoryRollAvg would be (1 + 14) / 7 = 2.14... However, what I would like to see is the average of all the NameRollAvg. [(1/7) + (14/7)] / 2 = 1.07...

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        6 years ago

        Yeah, no, still not buying it.  You already know that 14 is more than 1.  Fudging the average in that way is not constructive IMHO.