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 Cate...
  • 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. 

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.

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

      Why?

       

      If one person sells a huge amount while every other person sells a small amount, I don't want this CategoryRollAvg to be greatly skewed by that. So, it may look like a lot of people are severely underperforming but in reality only one person is greatly performing . Again, I want to use these two metrics to compare one person to the rest of their peers. Unless I'm completely missing something math related...

       

      Nonetheless, is this possible to implement?

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

        Mathematically all you are doing is dividing the NameRollAvg by the count of names. What for?