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
tj1947
Regular Visitor

Plot multiple moving averages on one line & column chart

I want to plot the moving average of concrete strength for multiple concrete mixes over time... on one chart.  Is this possible?

 

Data table looks somethign liks this:

 

MixBreak_StrengthBreak_Date
1516430001/01/2021
1516320001/02/2021
1516370001/05/2021
1516450001/06/2021
1516400001/07/2021
1517450001/01/2021
1517470001/02/2021
1517520001/05/2021
1517500001/06/2021
1517510001/07/2021

 

I've been able to create a chart that looks like this (different dataset but same principle)

 

tj1947_0-1631898901507.png

 

PROBLEM with the chart above:  The Moving Average line is for both Mix 1516 & Mix 1517.    What I want is 1 moving average line for Mix 1516 and a separate moving average line for Mix 1517.   The Mix Numbers can vary over time.  Next week, one Mix Number could be discontinued and another one added so I can't create separate measures for each mix using a Mix Number filter because the Mix Numbers can change over time.  

 

Is there a way to accomplish this?

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

Hi @tj1947 ,

I did some research and I'm sorry I didn't find any way to display it dynamically. Of course, if you find a better way, you can share it and mark it to help more people with the same problem.

 

Best Regards

Lucien

v-luwang-msft
Community Support
Community Support

HI  @tj1947 ,

Try to create measure like below:

1516aver = CALCULATE(AVERAGE('Table'[Break_Strength]),FILTER(ALL('Table'),'Table'[Mix]=1516&&MAX('Table'[Break_Date])>='Table'[Break_Date]))
1517aver = CALCULATE(AVERAGE('Table'[Break_Strength]),FILTER(ALL('Table'),'Table'[Mix]=1517&&MAX('Table'[Break_Date])>='Table'[Break_Date]))

Then create visual :

vluwangmsft_0-1632206428700.png

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

Yes, that works.  But that 'hard codes' the mix numbers.  My issue is that the mix numbers can change over time.  I need a solution that works dynamically and does not require me to edit the visual every time a mix number changes.  Not sure it's possible.

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.