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

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

Reply
Anonymous
Not applicable

Average - Select the Period in Minutes - Like a Moving Average

Hello all!

 

I have a set of data like this, with a record every 5 minutes:

 

3.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

When analyzing a period of days, for example, there is too much data and noise. I would like to "dampen" the values, so I thought about applying something like a moving average, but not quite. I would like to have the average for each "X" minutes. For example, If you set 15 minutes, the first one would average the 3 values of 00:00, 00:05 and 00:10 and so on. I would like to do this dinamically so you can set the interval on the report.

 

Any thoughts?

 

Thanks in advance! Man Wink

 

Regards.

1 ACCEPTED SOLUTION

Hi @Anonymous,

 

>>However, when I input my data I get this error:

The error will appear when you try to divide or mod the zero, you should add a condition to filter the zero range.

 

>>To be honest, I can't figure out how this doesn't work because I didn't get the logic of the "AvgPerRange

Below are some detail comment of my solution.

 

TimeRange table is used to set the column filed of matrix. if you try to merge this part to original table, it will group data and affect the original data, so I create a new table without any relationship to calculate the column range.

 

Select measure is used to get the select item from current table, if you limit them to measure level, it will get the current column value from matrix.

 

Calculated column Time Range is used to transfer the time to number type to calculate easily.

 

The logic of the "AvgPerRange" measure:

 

MAX([TimeRange])

Get current timeRange of the current date.

e.g: 1/31/2017 00:00:00 =0, 1/31/2017 00:15:00 = 15

 

var minRange= INT(MAX([TimeRange])/[select])

minRange variable is used to calculate the the dynamic date range based on current column value. (Lower limit of interval)

 

AVERAGEX(FILTER(ALL('Sheet1'),[TimeRange]>=minRange*[select]&&[TimeRange]<(minRange+1)*[select]),[Value])

Calculate the dynamic average based on current column value.

 

e.g: current column value 15,  datetime 1/31/2017 01:15:00 (timerange = 75)

dynamic formula:

AVERAGEX(FILTER(ALL('Sheet1'),[TimeRange]>=5*15&&[TimeRange]<6*15),[Value])

Get the average value from all of current table which the datetime is between 1/31/2017 01:15:00 to 1/31/2017 01:30:00

 

var state=MOD(HOUR(MAX([Time]))*60+MINUTE(MAX(Sheet1[Time])),[select])=0

The variable which used to control the if the average value is displaied.

 

In summary, the full logic is : get the dynamic average based on current time range and show the value in the correct cell.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

15 REPLIES 15
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

 

Based on my understanding, you want to get the dynamic average about the selected time, right?

 

If this is a case, you can refer to below formula which used to get the specify range from slicer and calculate the average based on it.

 

Measures:

 

SelectedTime = if(HASONEVALUE(Table[Time]),VALUES(Table[Time]),BLANK())

AverageOfSelectRangePerAttr(AllDate)= 
var currentAttr=LASTNONBLANK(Table[Attribute],[Attribute])
return
AVERAGEX(FILTER(ALL(Table),[Time]<[SelectedTime]&&[Attribute]=currentAttr),[Value]) 


AverageOfSelectRangePerAttr(CurrentDate)= 
var currentAttr=LASTNONBLANK(Table[Attribute],[Attribute])
return
AVERAGEX(FILTER(ALL(Table),[Time]<[SelectedTime]&&[Attribute]=currentAttr&&[Date]=MAX(Date]),[Value]) 


AverageOfSelectRange(AllDate)= 
AVERAGEX(FILTER(ALL(Table),[Time]<[SelectedTime]),[Value]) 

AverageOfSelectRange(CurrentDate)= 
AVERAGEX(FILTER(ALL(Table),[Time]<[SelectedTime]&&[Date]=MAX(Date]),[Value]) 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

@v-shex-msft

 

I unpivotted the data, like this below. Is this a good step?

 

Thanks.

 

Unpivot.jpg

 

 

Hi @Anonymous,

 

Sorry for slow response, I have an idea to achieve your requirement, perhaps you can take a look at blow steps:

 

1. Add a custom column "Minute" to convert the time column to number.

2. Use "Minute" column which created above as the source of slicer.

3. Write the measure to get the selected Item.

 

Selected Value = IF(HASONEVALUE('Table'[Minute]),VALUES('Table'[Minute]),BLANK())

 

 

4. Calculate based on slicer and "Minute".

 

Measure:

AvgPerRange= 
var minRange= INT(MAX([Minute])/[Selected Value])
return
AVERAGEX(FILTER(ALL('Table'),[Minute]>=minRange*[Selected Value]&&[Minute]<(minRange+1)*[Selected Value]),[Value])

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

@v-shex-msft

 

Sheng,

Thanks for your support!

But I couldn't get to the value I was looking for. I only got one value instead of multiple.

Anyway I think I didn't understand your formula 100%. I was not sure what to write on [Value]. The Sum of the column I want?

To make it clearer I made an excel sheet with a piece of data and a table with the desired output. It is below. I can send the file if you want to check it. The *.pbix as well Smiley Frustrated

Desired outputDesired output

 

 

 

 

 

 

 

 

 

 

 

 

 

If this is taking you too long, please just don't care. I don't want to push to hard on getting help Smiley Happy

Once again, I am trying to get this measure to avoid the ton of values I have now.Too many valuesToo many values

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks again! Smiley Very Happy

Hi @Anonymous,

 

You can refer to below sample file if it works on your side:

 

Test result:

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hello @v-shex-msft!

 

For your data, the solution works perfectly. However, when I input my data I get this error:

 

ErrorError

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Then, even with your original data, when I isolate the "MOD(HOUR(MAX([Time]))*60+MINUTE(MAX(Sheet1[Time])),[select])" on a new Measure, I get the same error.

 

To be honest, I can't figure out how this doesn't work because I didn't get the logic of the "AvgPerRange =
var minRange= INT(MAX([TimeRange])/[select])
var state=MOD(HOUR(MAX([Time]))*60+MINUTE(MAX(Sheet1[Time])),[select])=0
return
if(state, AVERAGEX(FILTER(ALL('Sheet1'),[TimeRange]>=minRange*[select]&&[TimeRange]<(minRange+1)*[select]),[Value]),BLANK())"

 

Could you briefly explain me, please?

 

From there, maybe I can detect the error and why it doesn't work for my data.

 

Thanks for your help and regards.

Hi @Anonymous,

 

>>However, when I input my data I get this error:

The error will appear when you try to divide or mod the zero, you should add a condition to filter the zero range.

 

>>To be honest, I can't figure out how this doesn't work because I didn't get the logic of the "AvgPerRange

Below are some detail comment of my solution.

 

TimeRange table is used to set the column filed of matrix. if you try to merge this part to original table, it will group data and affect the original data, so I create a new table without any relationship to calculate the column range.

 

Select measure is used to get the select item from current table, if you limit them to measure level, it will get the current column value from matrix.

 

Calculated column Time Range is used to transfer the time to number type to calculate easily.

 

The logic of the "AvgPerRange" measure:

 

MAX([TimeRange])

Get current timeRange of the current date.

e.g: 1/31/2017 00:00:00 =0, 1/31/2017 00:15:00 = 15

 

var minRange= INT(MAX([TimeRange])/[select])

minRange variable is used to calculate the the dynamic date range based on current column value. (Lower limit of interval)

 

AVERAGEX(FILTER(ALL('Sheet1'),[TimeRange]>=minRange*[select]&&[TimeRange]<(minRange+1)*[select]),[Value])

Calculate the dynamic average based on current column value.

 

e.g: current column value 15,  datetime 1/31/2017 01:15:00 (timerange = 75)

dynamic formula:

AVERAGEX(FILTER(ALL('Sheet1'),[TimeRange]>=5*15&&[TimeRange]<6*15),[Value])

Get the average value from all of current table which the datetime is between 1/31/2017 01:15:00 to 1/31/2017 01:30:00

 

var state=MOD(HOUR(MAX([Time]))*60+MINUTE(MAX(Sheet1[Time])),[select])=0

The variable which used to control the if the average value is displaied.

 

In summary, the full logic is : get the dynamic average based on current time range and show the value in the correct cell.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

@v-shex-msft

 

Just one more thing please, probably quite easy to solve Man LOL I am having trouble applying this because I have a Calendar Table, like below. The value I am looking to apply the average is 'Flowmeter Data'[Value].

 

RelationsRelations

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

So, the Tables and Columns look this this:

Tables and ColumnsTables and Columns

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

And the added fields are:

 

Time = VALUES('Calendar'[Time])

 

Range = if(MINUTE([Time])=0,,60,,MINUTE([Time]))

 

Select TEST = if(HASONEVALUE('TimeRange'[Range]),VALUES('TimeRange'[Range]),BLANK())

 

TimeRange = HOUR([Time])*60+MINUTE([Time])

 

AvgPerRange TEST =
var minRange= INT(MAX('Flowmeter Data'[TimeRange])/[Select TEST])
var state=MOD(HOUR(MAX('Flowmeter Data'[Time]))*60+MINUTE(MAX('Flowmeter Data'[Time])),[Select TEST])=0
return
if(state, AVERAGEX(FILTER(ALL('Flowmeter Data'),[TimeRange]>=minRange*[Select TEST]&&[TimeRange]<(minRange+1)*[Select TEST]),'Flowmeter Data'[Value]),BLANK())

 

I can't show the data as it just calculates something and it never really appears on the table. I think this is because of the relations and the inputs on the measure AvgPerRange TEST

 

What do you think?

 

Regards.

Hi @Anonymous,

 

>>I can't show the data as it just calculates something and it never really appears on the table. I think this is because of the relations and the inputs on the measure AvgPerRange TEST

 

If you can please provide some sample file and share the link, I will check it.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hello @v-shex-msft

 

Sure, it is attached.

 

Thank you AGAIN Smiley Wink

Hi @Anonymous,

 

Based on test, I find the issue is caused by total row and total column, you should use other columns to turn off these feature first, then the visual will works smoothly.

 

Screenshot:

 

Capture(1).PNG 

 

BTW, I modify the measure to allow filter works on it.

 

AvgPerRange = 
var minRange= INT(MAX([TimeRange])/[select])
var state=MOD(HOUR(MAX([Time]))*60+MINUTE(MAX('Flowmeter Data'[Time])),[select])=0
return
if(state, AVERAGEX(FILTER(ALLSELECTED('Flowmeter Data'),[Date]=MAX([Date])&&[TimeRange]>=minRange*[select]&&[TimeRange]<(minRange+1)*[select]),[Value]),BLANK())

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Perfect!

 

Thank you so much Man Happy Robot Happy Smiley Happy

 

The result is below.

 

Regards.

 

SampleSample

 

 

Anonymous
Not applicable

@v-shex-msft

 

Thanks a lot! I got it.

 

I also understood the cause for the MOD error... It was due to the Range = if(MINUTE([Time])=0,60,MINUTE([Time]))

 

Stupid error. I don't know why but on DAX, to input a value I have to put the decimal separator (",") even if it is an integer. This breaks my balls. Like this: Range = if(MINUTE([Time])=0,,60,,MINUTE([Time]))

 

Thanks, I will use this techniques for other Dashboards, most certainly!

 

Smiley Very Happy Smiley Happy

Anonymous
Not applicable

Any thoughts on this, anyone? Smiley Happy

Anonymous
Not applicable

Sheng,

 

Thanks for your reply.

 

I tried your measures but that didn't work as expected, and I didn't understand how it works as well. Let me give you more info, I think it will be easier to understand.

 

First let me show you the relationships.

 

Relationships.jpg

 

I have, among others, 2 tables that I would like to relate.

Those being 'Flowmeter Data' and 'SCADA Data'.

 

In 'SCADA Data', the values are unpivotted by [Attibute].

 

In 'Flowmeter Data', each line is a value with multiple columns.

BTW, should I unpivotted this for the sake of data organization?

 

 

 

 

 

 

 

 

 

 

 

 

 

So I would like to do this operation that I mentioned for the 2 tables. For example, if I select 5 minutes the data would not be changed but if I selected 30 minutes, I would get the average from 00:00 to 00:25, then 00:30 to 00:55, and so on.

 

Maybe there's something you can add now?

 

Thanks Smiley Wink

 

 

Flowmeter Data.jpg

 

 

SCADA Data.jpg

 

 

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.