Data Smoothing (moving average in matrix)

Matt_P may be if you can share example what seems wrong and what suppose to be the correct value then I can look into it.

Matt_P This is helpful, I will look into it today. 

Matt_P shouldn't be an issue if you can share the calculation example in excel as you did it previously.

Matt_P got it now I know what you are after. Will do.

Matt_P sounds good. I'm surely missing something, excel sheet will help.

https://1drv.ms/x/s!AsRFZB1O95IYf_29aQ99322rCLM

Link to above spreadsheet.

Hi parry2k , the power BI gives an answer of 123 when the target is 600.  i.e. just the yellow highlighted cells.  I would like the whole box of values totaled and divided by 25.  eg If i want to calculate the cell in the averages matrix that is for 55,3000 then the boxed values contained within the area given by +2 & -2 rows plus +2 & -2 columns are taken from the data, added and divided by 25. (result is 14995/25 = 599.8 (600))

Again thanks for your help!

parry2k No problem at all regarding the delay.  Absolutly fantastic result!!  Thanks very much for your help!!

parry2k Hi there - rather stuck again.  The matrix works, i now need to use the generated data to get a new matrix where each value in the previous matrix (smoothed) is divided by the grand total of the previous matrix(smoothed).  Nothing I try seems to work...

Thanks!

parry2k thanks!!!

Here is a link to the spreadsheet.  https://1drv.ms/x/s!AsRFZB1O95IYgQBlPwy1W79z07wL

Ive not completed all the data however, the 2nd matrix is what im after for the whole dataset.

Again many thanks - learning such alot!!

Matt_P glad i could help. The pbix file I sent to you there is already a measure called "Total Qty" which is actually a grand total of the whole dataset. You can use that to divide and it will give you the result you are after. If you run into issue, reach out. 

Matt_P  probably I was already thinking in advance  and for some reason added that measure :smileyhappy:

parry2k  I saw the total,  its the total of the smoothed data though is it?  And its how to get the 2nd matrix of data as per the spreadsheet =)

Matt_P Total Qty field is grand total of original Qty column. to divide smoothing avg field with total qty, just add another measure

Smooth Avg Divided by Total Qty =
DIVIDE( [Smoothing Avg], [Total Qty] )

parry2k thanks but im after grand total of the smoothing average matrix.  Then the smoothing martix divided by that to create another matrix... So still a tad stuck. :smileyhappy:

Ok, so i've got as far as getting one of the cells calculated using a command to generate a new table.

Matt_P try following two measures

Smoothing Avg Total = SUMX( FlatData, [Smoothing Avg] )

Smoothing Avg % = DIVIDE( [Smoothing Avg], [Smoothing Avg Total] )

parry2k Hey, thanks for trying - doesnt provide the result im after though.  I'm getting an excel sheet together to illustrate what im after - will likely be tomorrow evening...

Thanks again.

parry2k Hey

Here is a link to the spreadsheet: https://1drv.ms/x/s!AsRFZB1O95IYgQNly1A0H1m-SGjQ

Again thanks for your help!!

Matt_P is the data in excel sheet same what we used in smoothing report?

Thats it - genius - absolutley fantastic - thankyou!!!

On to stage 2...

I need to do some more work to the excel to explain what im after, and I will try to repicate it in PowerBI myself first... dont hold much hope of that given past experiance.  I Just hope your still about to assist... :smileyhappy::smileyhappy:

Matt_P glad it met the requirement. BRING IT ON :)

Matt_P  could you please provide the FlatData.xlsx again?

I'd like to take a closer look into it.