Measure to get average of a subset whose size is defined by user

[ichascoe]

Hi 

I am working on a report that shows several statistical calculations (average, moving range, USL, LSL, etc.). The problem comes when I try do this calculation to a subset of the data. I would like to create, in example, a measure that get the average grouped by 3 by 3 rows. With table below, It should get average of green values and other average value of blue ones.

Date	Value
20/12/2019 12:50	2400
20/12/2019 11:10	2378
19/12/2019 10:54	2532
18/12/2019 10:20	1356
17/12/2019 9:58	1285
01/12/2019 9:53	2465

I have been investigating groups and bining functions of Power BI and I have achieved to calculate average every 3 days but It´s not the same because It depends on days and not in number of rows so first subset includes day 20-19-18. This function only works with existing columns values, not with number of rows. I have been thinking about creating a new column with an autoincrement value, the problem is that data can be filtered by other columns so it wouldn´t work because the indexes wouldn´t be consecutives.

can anyone help or guide me?

Thanks

[v-zhenbw-msft · Accepted solution]

Hi @ichascoe ,

We can create a measure to meet your requirement.

Measure 3 = 
var x = [Measure]
var max_date = CALCULATE(MAX('Table'[Date]),FILTER(ALLSELECTED('Table'),[Measure]=x))
var y = MAX('Table'[Date])
return
IF(
    MAX('Table'[Date])=max_date ,[Measure 2],BLANK())

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.       

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ichascoe]

That´s perfect! thanks a lot. Just to complete the answer because maybe it can be usefull for someone, I have created another 2 measures in order to calculate moving range:

M2MovingRange = 
var x = [M1]
var x_prev =[M1]-1
var sum_ = CALCULATE(SUM('Table2'[Value]),FILTER(ALLSELECTED('Table2'),[M1]=x))
var count_ = CALCULATE(COUNT('Table2'[Date]),FILTER(ALLSELECTED('Table2'),[M1]=x))
var sum_prev = CALCULATE(SUM('Table2'[Value]),FILTER(ALLSELECTED('Table2'),[M1]=x_prev))
var count_prev = CALCULATE(COUNT('Table2'[Date]),FILTER(ALLSELECTED('Table2'),[M1]=x_prev))

return
if(x_prev=0,blank(),abs(DIVIDE(sum_,count_) - divide(sum_prev,count_prev)))

M3MovingRange = 
var x = [M1]
var max_date = CALCULATE(MAX('Table2'[Date]),FILTER(ALLSELECTED('Table2'),[M1]=x))
var y = MAX('Table2'[Date])
return
IF(
    MAX('Table2'[Date])=max_date ,[M2MovingRange],BLANK())

[v-zhenbw-msft]

Hi @ichascoe ,

We can use the following steps to meet your requirement.

1. Create a new parameter.

2. Create a measure to group by the table based on parameter.

Measure =
IF (
    [Row_number Value] = 0,
    1,
    ROUNDDOWN (
        DIVIDE (
            RANKX (
                CALCULATETABLE ( DISTINCT ( 'Table'[Date] ), ALLSELECTED () ),
                [Date],
                MAX ( [Date] ),
                ASC,
                DENSE
            ) - 1,
            [Row_number Value]
        ),
        0
    ) + 1
)

3. Then we can create a measure to calculate the average.

Measure 2 = 
var x = [Measure]
var sum_ = CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),[Measure]=x))
var count_ = CALCULATE(COUNT('Table'[Date]),FILTER(ALLSELECTED('Table'),[Measure]=x))
return
DIVIDE(sum_,count_)

We can get the result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.       

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ichascoe]

Thanks a lot!

It´s similar but not exact. For example for a this table and a size=2

Date	Value
01/12/2019	2465
17/12/2019 9:58	1285
18/12/2019 10:20	1356
19/12/2019 10:54	2532
20/12/2019 11:10	2378
20/12/2019 12:50	2400

The result should a table of 3 rows (#samples/size) with the last date of the group, the average of the values of the group and de moving range (average of current group - average of previous group):

Date	Average	Moving range
17/12/2019 9:58	1875
19/12/2019 10:54	1944	69
20/12/2019 12:50	2389	445

Thanks & best regards