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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cbruhn42
Helper II
Helper II

Calculate Average of Last 25 Data Points

I have a data set that will continue to add records over time, but I want to average the last 25 data points and then plot it on a line chart as a straight line.  However, when I plot it my measure is recalculating the average at each date using the previous 25 data points from that date.   If I put the measure in a card I get the answer I'm looking for though.

 

Here is the measure I've created using an example I found on the web.

 

VAR thisdate = 
MAX ('Final FV Alcolyzer Data (2)'[Alcolyzer DT])
 
VAR Last25DataPoints =
TOPN (
25,
CALCULATETABLE (
DISTINCT ('Final FV Alcolyzer Data (2)'[Alcolyzer DT]),
ALL ('Final FV Alcolyzer Data (2)'[Alcolyzer DT]),
'Final FV Alcolyzer Data (2)'[Alcolyzer DT] <= thisdate
),
'Final FV Alcolyzer Data (2)'[Alcolyzer DT], DESC
     )
RETURN
CALCULATE (
Average ('Final FV Alcolyzer Data (2)'[Value]),
Last25DataPoints, REMOVEFILTERS('Final FV Alcolyzer Data (2)'[Alcolyzer DT])
  )
 
I though the REMOVEFILTERS function would resolve the issue for me in the chart, but it is not.  So my chart has 'Final FV Alcolyzer Data (2)'[Alcolyzer DT] on the x-axis and that is also what I'm using in my date slicer.
 
Chart looks like this:
 
cbruhn42_0-1684506067154.png

 

I have the average calculated for the entire data set on here is well.

 

Thank you for the help!

17 REPLIES 17
cbruhn42
Helper II
Helper II

Any other thoughts?

Hi, 

 

My understanding is that you want to preserve all filters in your selection except the date filter. In order to ignore the data filter column selection, you simply add the column within your ALL clause. Doing so will ignore your date selection but presever all other filter selections. 

 

Try the following 

WaqarArshad_0-1685431581530.png

 

Another thing I just thought of is that if I have a brand that doesn't have 25 data points yet the measure returns 'blank'.  How can I make it just use however many data points I have to calculate the average and then I'll make a card on the display indicating we don't have 25 data points or something.

Guessing some sort of IF statement?

I did this and it does now give me a static average, but when I export the data to double check the average is incorrect.  It should be 6.42 for the example I'm looking at for the last 25 values but my Power BI measure is returning 6.37.

 

Here is the data set I'm looking at:

DTValue
5/21/2023 11:176.41
5/19/2023 16:226.32
5/14/2023 12:176.42
5/9/2023 11:476.33
5/9/2023 9:596.36
4/30/2023 14:056.43
4/26/2023 11:036.4
4/21/2023 10:346.29
4/18/2023 11:266.67
4/14/2023 12:346.55
4/11/2023 12:286.43
4/7/2023 10:346.41
4/5/2023 11:456.41
4/2/2023 12:116.54
3/30/2023 9:526.54
3/22/2023 15:546.39
3/21/2023 10:166.48
3/15/2023 15:186.46
3/6/2023 12:296.44
3/5/2023 13:446.49
3/1/2023 13:386.34
2/27/2023 17:486.34
2/21/2023 11:026.36
2/20/2023 10:416.37
2/19/2023 16:546.37
2/12/2023 12:286.27
2/8/2023 12:216.32
2/7/2023 11:586.36
1/23/2023 12:506.45
1/11/2023 11:026.39
1/10/2023 12:496.39
1/3/2023 11:256.5
12/27/2022 13:226.51
12/27/2022 13:106.39
12/22/2022 13:146.36
12/16/2022 13:106.43
12/15/2022 11:056.43
12/5/2022 11:096.47
11/20/2022 11:306.47
11/17/2022 12:376.74
11/14/2022 10:256.67
11/11/2022 13:386.66
10/31/2022 10:316.68
10/31/2022 10:276.45
10/27/2022 10:146.63
10/24/2022 11:256.48
10/12/2022 9:486.59
10/10/2022 11:556.61
10/5/2022 9:156.67
10/5/2022 9:116.66
9/30/2022 11:276.62
9/25/2022 14:436.68
9/22/2022 10:116.73
9/18/2022 15:096.64
9/18/2022 15:056.55
9/9/2022 14:096.61
9/9/2022 14:056.74
9/7/2022 9:556.74
9/1/2022 9:366.66
8/24/2022 9:496.27
8/21/2022 13:086.26
8/12/2022 8:196.8
8/7/2022 13:096.64
8/7/2022 13:056.68
8/3/2022 15:026.66
7/28/2022 10:236.59
7/22/2022 10:496.65
7/20/2022 10:166.72
7/17/2022 14:446.61
7/14/2022 10:066.62
7/8/2022 11:576.52
7/6/2022 10:056.64
7/4/2022 12:376.72
6/27/2022 9:386.63
6/24/2022 13:086.8
6/21/2022 10:216.77
6/17/2022 12:386.79
6/13/2022 9:336.67
6/6/2022 9:436.84
WaqarArshad
Helper I
Helper I

Yes that will do the trick as all selected only removes filters from current query while preserving filters from outer query

WaqarArshad
Helper I
Helper I

Hi, see attached image. Does this resolve your issue?

 

LastXDataPoints = CALCULATE(AVERAGE(Data[Value]), TOPN(25, ALL(Data), Data[Date], DESC))​
 

 

 

WaqarArshad_1-1684935130227.png

 

This has me close!  I have a straight, static line now, but it's averaging all the [value] data points together and ignoring my attribute slicer on the page.  

 

I changed ALL to ALLSELECTED and it worked!

Turns out it didn't work.  When I adjust my date slicer the average is changing for some reason.  

What I have is a table of brands that have attributes and then values associated to those attributes.  I am filtering by brand, attribute and analysis date and then displaying the control chart with data points and control limits.  I need the average and standard deviation calculated using the last 25 data points, but so far everything I have tried doesn't give me static numbers.  They're chaning based on the date slider.  HEre is the latest attemp that is not working:

 

Attribute Avg Last 25 data points =

CALCULATE(Average ('Final FV Alcolyzer Data (2)'[Value]), TOPN(25, ALLSELECTED('Final FV Alcolyzer Data (2)'), 'Final FV Alcolyzer Data (2)'[Alcolyzer DT], DESC), REMOVEFILTERS('Final FV Alcolyzer Data (2)'[Alcolyzer DT]))
 
I also tried this:
 
Attribute Avg Last 25 data points =

CALCULATE(Average ('Final FV Alcolyzer Data (2)'[Value]), TOPN(25, ALL('Final FV Alcolyzer Data (2)'), 'Final FV Alcolyzer Data (2)'[Alcolyzer DT], DESC))

Figured something out.  It's calculating the average and std dev as long as the date range I've selected has 25 data points or more in it.  If it has less then it calculates it off of however many data points are in my date range.

 

My problem is still this.  I have hundreds of data points in this data set for each attribute.  I should still be able to calculate the stats on the last 25 data points regardless of what my date range is set at.  How do I do that though?

WaqarArshad
Helper I
Helper I

Are you able to share sample data or a sample pbix?

So here's an example.

The last 5 data points in my data set are below and each analyzed on a different day.

6.28

6.42

6.33

6.36

6.43

 

The average is 6.36 and I'd like that to be displayed as a single value across the chart and then plot the data points also on the chart.  I was able to calculate the average across the entire data set and plot this using REMOVEFILTERS to get my straight line.

 

 

Here is the issue I'm seeing.  You can see I have 25 data points in my date range and so the regular average of the value is correct at 6.42.  The measure is returning 6.37 and I can't figure it out.

 

cbruhn42_0-1685485145353.png

 

So did you maange to resolve this using REMOVEFITLERS?

No, the removefilters function doesn't change anything.  I'm not sure if I have it in the wrong spot or what.

WaqarArshad
Helper I
Helper I

Hi, 

 

Without the pbix, its difficult to test this but could you try the following.

 

 Last25DataPoints = CALCULATE(Average ('Final FV Alcolyzer Data (2)'[Value]), TOPN(25, ALL('Final FV Alcolyzer Data (2)'), 'Final FV Alcolyzer Data (2)', DESC))

That produced this error:

cbruhn42_0-1684512778687.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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