Average of last 3 values and next 3 values

[Mahadevaraobc]

Hello Experts,

I have a table as following

Product	Sales	no of stores	% Conversion
A	325509	259	0.95%
A	310352	281	1.09%
A	321934	275	1.03%
A	319762	291	1.09%
A	327509	280	1.03%
A	320436	291	1.09%
A	306302	334	1.31%
A	296351	238	0.96%
A	24948	16	0.77%
A	28846	12	0.50%
A	27870	27	1.16%
A	30454	18	0.71%
A	30065	10	0.40%
B	29767	8	0.32%
B	35922	21	0.70%
B	39480	11	0.33%
B	40592	14	0.41%
B	41127	16	0.47%
B	38268	22	0.69%
B	25217	15	0.71%
B	18070	16	1.06%
B	16965	10	0.71%
B	16683	19	1.37%
B	15762	14	1.07%
B	15057	7	0.56%
B	13587	11	0.97%
C	5679	7	1.48%
C	8761	5	0.68%
C	7167	3	0.50%
C	4873	5	1.23%
C	6557	6	1.10%
C	6469	7	1.30%
C	6452	5	0.93%
C	12509	8	0.77%
C	6421	9	1.68%
C	6417	1	0.19%
C	6414	4	0.75%
C	6410	3	0.56%
C	6409	5	0.94%
C	6408	6	1.12%

I would like to know how to get an average of 7 values based on a highest sales condition.

ex: Calculate highest value of sales in Product A which is 327509 and based on this value check what is the % conversion (1.03%), now calculate average of % conversion for last 3 values and next 3 values including the current value (Average of total 7 values). In this case it will be 1.08%

1.09%

1.03%

1.09%

1.03%

1.09%

1.31%

0.96%

Thanks in advance for your help.

[tamerj1]

@Mahadevaraobc 
Here is the updated sample file https://we.tl/t-oXVgQzVfA8

% Converion = 
VAR CurrentIndex = MAX ( Data[Index] )
VAR CurrentNumOfStores = MAX ( Data[no of stores] )
VAR CurrentSales = MAX ( Data[Sales] )
VAR CurrentProductTable =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Product] ) )
VAR MaxSales = MAXX ( CurrentProductTable, Data[Sales] )
VAR IndexOfMaxSales = 
    MAXX ( FILTER ( CurrentProductTable, Data[Sales] = MaxSales ), Data[Index] )
VAR Result = DIVIDE ( CurrentNumOfStores * 12, CurrentSales )
RETURN
    IF ( CurrentIndex >= IndexOfMaxSales - 3 && CurrentIndex <= IndexOfMaxSales + 3, Result )

[tamerj1]

@Mahadevaraobc

If your data is not already aggregated then you need to create an index measure based  in the original index then we need to modify our formula. It would more complex but can be achieved. If this is the case then please provide me with sample raw dat. 

[Mahadevaraobc]

Hi @tamerj1 ,

Attaching an excel file which has info and reqd field,i have marked highest value in red and avg of 7 months in yellow for reference.

How to attach xlsx file not able to find an option to attach

[tamerj1]

@Mahadevaraobc 
I see nothing attched. However, I updated the solution as requested. https://we.tl/t-n5VCL2koo1

The filter measure is as per the first version of the solution then a new Average % Converion measure as follows:

%Conversion = AVERAGEX ( ALLSELECTED ( Data[Index] ), CALCULATE ( SUM (Data[% Conversion] ) ) )

[Mahadevaraobc]

Hi @tamerj1 ,

I think we are close but my expectations is as below screenshot. 

[tamerj1]

@Mahadevaraobc 

Avergare % Conversion = 
VAR CurrentIndex = MAX ( Data[Index] )
VAR CurrentProductTable =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Product] ) )
VAR MaxSales = MAXX ( CurrentProductTable, Data[Sales] )
VAR IndexOfMaxSales = 
    MAXX ( FILTER ( CurrentProductTable, Data[Sales] = MaxSales ), Data[Index] )
VAR FilteredTable = FILTER ( CurrentProductTable, Data[Index] >= IndexOfMaxSales - 3 && Data[Index] <= IndexOfMaxSales + 3 )
RETURN
    AVERAGEX ( FilteredTable, Data[% Conversion] )

[Mahadevaraobc]

Hi @tamerj1 ,

Thank you but the expected value shown above is different from the value i get in excel.

ex: Avg of Product A is 1.08%, Avg of Product B is 0.63% but the value you are getting is 1.09% and 0.62%. 

[tamerj1]

@Mahadevaraobc 

Apparently it is a rounding issue 

[Mahadevaraobc]

Hi @tamerj1 ,

The Filter Measure is showing 1 correctly but not %conversion, in above case product A average should be 1.08% (average of values % Conversion column) for all values, but sample shows 0.93% for all ( taking an average of all values in Product A, it should only take avg of Filter Measure value is 1)

[Mahadevaraobc]

Hi @tamerj1 ,

Thanks for helping, our table already has a %conversion column our needs are mentioned below.

1) check max sales in the table 

2) get value of %conversion

3) get values of +3 months values and -3 months

4) make an average based on these 7 values and show in different column

ex: 

Product A max sales = 327509 which %conversion is 1.03%

Date	Product	Sales	no of stores	% Conversion	expected result
201611	A	325509	259	0.95%	1.08%
201612	A	310352	281	1.09%	1.08%
201701	A	321934	275	1.03%	1.08%
201702	A	319762	291	1.09%	1.08%
201703	A	327509	280	1.03%	1.08%
201704	A	320436	291	1.09%	1.08%
201705	A	306302	334	1.31%	1.08%
201706	A	296351	238	0.96%	1.08%
201707	A	24948	16	0.77%	1.08%
201708	A	28846	12	0.50%	1.08%
201709	A	27870	27	1.16%	1.08%
201710	A	30454	18	0.71%	1.08%

[tamerj1]

Hi @Mahadevaraobc 
Plesae refer to sample file with the solution https://we.tl/t-Lph83wQrrO

[Mahadevaraobc]

Hi @tamerj1 ,

Its working and showing me 1 as expected , but how can i display average of %conversion in place of 1.

ex: 1s should be replaced by averaging all 7 values of %conversion. 

[tamerj1]

@Mahadevaraobc 
Please define average of %conversion

[Mahadevaraobc]

@tamerj1 

%of conversion will be no of stores*12/sales.

[Mahadevaraobc]

Hi @tamerj1 ,

Here it is

Date	Product	Sales	no of stores	% Conversion
201611	A	325509	259	0.95%
201612	A	310352	281	1.09%
201701	A	321934	275	1.03%
201702	A	319762	291	1.09%
201703	A	327509	280	1.03%
201704	A	320436	291	1.09%
201705	A	306302	334	1.31%
201706	A	296351	238	0.96%
201707	A	24948	16	0.77%
201708	A	28846	12	0.50%
201709	A	27870	27	1.16%
201710	A	30454	18	0.71%
201711	A	30065	10	0.40%
201611	B	29767	8	0.32%
201612	B	35922	21	0.70%
201701	B	39480	11	0.33%
201702	B	40592	14	0.41%
201703	B	41127	16	0.47%
201704	B	38268	22	0.69%
201705	B	25217	15	0.71%
201706	B	18070	16	1.06%
201707	B	16965	10	0.71%
201708	B	16683	19	1.37%
201709	B	15762	14	1.07%
201710	B	15057	7	0.56%
201711	B	13587	11	0.97%
201611	C	5679	7	1.48%
201612	C	8761	5	0.68%
201701	C	7167	3	0.50%
201702	C	4873	5	1.23%
201703	C	6557	6	1.10%
201704	C	6469	7	1.30%
201705	C	6452	5	0.93%
201706	C	12509	8	0.77%
201707	C	6421	9	1.68%
201708	C	6417	1	0.19%
201709	C	6414	4	0.75%
201710	C	6410	3	0.56%
201711	C	6409	5	0.94%
201712	C	6408	6	1.12%

Date is in YYYYMM format

[Mahadevaraobc]

Hi @tamerj1 ,

Yes there is a date field 

[Mahadevaraobc]

Hi @tamerj1,

Thanks for helping,

Product C has maximum value in between which is 12509.

[tamerj1]

@Mahadevaraobc 

Thank you. Do you gave a date or index column?

[tamerj1]

Hi @Mahadevaraobc 

do you have a date or index column? This shall be per each product? For prouct C there is only one value before the maximum one. In this case only 5 records will be displayed or they must be 7 records anyway?