cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

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

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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.

Frequent Visitor

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())``````

Community Support

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.

Frequent Visitor

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors