Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ichascoe
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:502400
20/12/2019 11:102378
19/12/2019 10:542532
18/12/2019 10:201356
17/12/2019 9:581285
01/12/2019 9:532465

 

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
v-zhenbw-msft
Community Support
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())

 

Measure F2.jpg

 

Measure F1.jpg

 

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.

View solution in original post

4 REPLIES 4
v-zhenbw-msft
Community Support
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())

 

Measure F2.jpg

 

Measure F1.jpg

 

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.

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

 

Capture.PNG

v-zhenbw-msft
Community Support
Community Support

Hi @ichascoe ,

 

We can use the following steps to meet your requirement.

 

1. Create a new parameter.

 

Measure 1.jpg

 

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
)

 

Measure 2.jpg

 

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,

 

M 3.jpg

 

M 4.jpg

 

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.

Thanks a lot!

 

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

 

DateValue
01/12/20192465
17/12/2019 9:581285
18/12/2019 10:201356
19/12/2019 10:542532
20/12/2019 11:102378
20/12/2019 12:502400

 

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):

 

DateAverageMoving range
17/12/2019 9:581875 
19/12/2019 10:54194469
20/12/2019 12:502389445

 

Thanks & best regards

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.