Categorical Rolling Average

[t-dahen]

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.

Name	Category	Date	Sales
Bob	A	6/19/2020	10
Alice	B	6/19/2020	13
Bob	A	6/19/2020	12
Joe	B	6/19/2020	15
Bob	A	6/20/2020	14

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.

[lbendlin]

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]

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]

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]

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]

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

[t-dahen]

@lbendlin 

Dividing the Summation of all NamRollAvg by the number of names. I want to see on average how many sales/week per name in a category:

Name	NameRollAvg
Bob	4
Alice	5
Joe	6

All belonging to the same category... The people in this category have around 3 sales per week. I am running this average not on raw sales but the derived NameRollAvg

[lbendlin]

Bob: 28

Alice: 35

Joe: 42

28+35+42 = 105

105 / 7 = 15

15 / 3 = 5

Same result as if you would do (4+5+6) / 3

[t-dahen]

@lbendlin 

Ah I see math fails me, or I fail math.  Thank you.

Now on to  PowerBI, How would I use a filter to to get this CategoryRollAvg when a name is selected?

[lbendlin]

put the category for the filter context in a variable, then in the calculate use an ALL() filter against the names and a category filter against your variable.  You probably need another variable to calculate the count distinct of names in your category.

[stevedep]

@t-dahen , If I understand your requirement correctly you want to calculate a running average of sales for a person regarding the category currently displayed. Then divide this number to the average of other peoples sales average in the last 7 days for this category: When viewed in excel it looks like this:

In this case the running salesaverage for Alice (in the last 7 days for category B) is 4% higher than the average of the peoples runningsales average. 

In here you see it working with some debug info:

This was made with the following dataset:

Pls mind that it uses a related date table. 

The DAX code:

RunningAverageNameCategorySales = 
IF(NOT(ISBLANK(MIN('Table'[Sales])));
CALCULATE(AVERAGE('Table'[Sales]);ALLEXCEPT('Table';'Table'[Name];'Table'[Category]);DATESINPERIOD('Date'[Date]; MIN('Date'[Date]);-7;DAY));
BLANK())

And:

PersonswithRunningsalesincat = 
CALCULATE(
    CONCATENATEX('Table';" RunningSales for: " & [Name] & ": " & [RunningAverageNameCategorySales] & "
");
ALLEXCEPT('Table';'Date';'Table'[Category]))

And:

AverageOfAverageRunningSalesinCat = 
CALCULATE(
    AVERAGEX('Table'; [RunningAverageNameCategorySales] );
ALLEXCEPT('Table';'Date';'Table'[Category]))

And:

ComparingRunningAverageWithRestInCategory = [RunningAverageNameCategorySales]/[AverageOfAverageRunningSalesinCat]

Power BI file here. 

Excel file here. 

Does it help? Thumbs up would be great. Did it answer your question or solve the challenge? Please mark as solution.

Kind regards, Steve.  

[t-dahen]

@stevedep 

Thank you for the response. Almost exactly what I need! Some questions.

Shouldn't RunningAverageNameCategorySales and AverageOfAverageSalesinCat both be 22 for Bob on 6/19 and both be 18 on 6/20? For 6/19, Bob has had 22 Sales. Although there were 2 entries (10 + 12), these numbers should aggregate for the day. For 6/20, Bob has 14 Sales + 22 Sales (from 6/19). His rolling 7-day average on this day should be 18.

-

Ultimately, the visual I want looks something like this. Notice there is no Category selected.

But since there is no Category selected, there is no context and the percentages are messed up. Even though Alice and Bob are in different categories, I would still like to compare their "performance"  visually.

[stevedep]

@t-dahen , I checked the numbers again, and they are correct, it can easily be verified with the 'debug' column 'PersonswithRunningsalesincat'. This of course assumes that category is available. Please note, I added some additional entries to your example data.

If you would like to compare people by looking across the categories you will need to define an additional aggregate calculation to compare people. So per person, you could average their performance (the percentage score we have already made) across categories. 
The code would then be:

ComparingRunningAverageWithRestInCategory = 
AVERAGEX(VALUES('Table'[Category]);
[RunningAverageNameCategorySales]
/
[AverageOfAverageRunningSalesinCat])

[t-dahen]

@stevedep 

Shouldn't Bob's RunningAverageNameCategorySales be 22 because we are looking at a daily level? This seems like you are counting Bob's two entries for 6/19 as two seperate Names in the Category.  This will be problematic if we introduce a new Name in Category A:

The AverageOfAverageRunningSalesinCat (Category A ) for 6/19 should be (22 + 15) / 2 = 18.5.  The duplicate "RunningSales for Bob: 11" is throwing things off.
-

That worked for the visual! Thanks!

[stevedep]

@t-dahen , I have given this some more time. This should work as you expect. Per person, the average category performance is calculated, by averaging performance per category. The performance per category is calculated by comparing the running average for the category by the average of all people selling in this category. I made a new file with detailed debug info allowing you to see exactly how the performance is calculated:

Now the final calculation is:

_0_PersonPerformance = AVERAGEX(VALUES('Table'[Category]);[_1_Measure_ComparingRunningAverageWithRestInCategory])

_1_Measure_ComparingRunningAverageWithRestInCategory = [_2_Measure_RunningAverageNameCategorySales]/[_1_Measure_AverageOfAverageRunningSalesinCat]

_1_Measure_AverageOfAverageRunningSalesinCat = 
CALCULATE(
    AVERAGEX('Table'; [_2_Measure_RunningAverageNameCategorySales] );
ALLEXCEPT('Table';'Date';'Table'[Category]))

_2_Measure_RunningAverageNameCategorySales = 
IF(NOT(ISBLANK(MIN('Table'[Sales])));
CALCULATE(AVERAGE('Table'[Sales]);ALLEXCEPT('Table';'Table'[Name];'Table'[Category]);DATESINPERIOD('Date'[Date]; MIN('Date'[Date]);-7;DAY));
BLANK())

p.s. I added more sample data to ensure proper testing was possible. 

Please mark as solution if this is what you were looking for. Appreciate thumbs up for the effort.

Kind regards, Steve. 

[t-dahen]

@stevedep 

I don't think I am being clear... Hear is an example of the calculations I would like to see. I would then like to plot the highlighted columns against dates. The 30.75 for Cateogry B  (6/20) Is coming from the Average of the RollAvg of people in Category B (Alice: 41.5 and Joe: 20). I got 41.5 for Alice's RollAvg (6/20) from averaging her total daily sales (13 and 70). Also, a Name will always be associated with one Category so I changed Alice's 6/20 Category to be B.

Date	Name	Category	Sales	RollAvg	CategoryAvg	RollAvg/Category
19-Jun	Bob	A	10
19-Jun	Bob	A	12	22	22	1
19-Jun	Joe	B	15	15	14	1.071429
19-Jun	Alice	B	13	13	14	0.928571
20-Jun	Bob	A	14	18	18	1
20-Jun	Joe	B	25	20	30.75	0.650407
20-Jun	Alice	B	30
20-Jun	Alice	B	40	41.5	30.75	1.349593

[stevedep · Accepted solution]

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

[t-dahen]

Perfect!! Thank you. 

[stevedep]

Welcome!