Solved: Data Smoothing (moving average in matrix)

Matt_P · ‎03-30-2019

I have a table of data as opposite and I would like to represent it as a heat map; rows = length, columns = weight, values = qty, which ive done in excel and PBI (PBI attached). Now I would like to 'smooth' out the differences between the cells in the heat map, perhaps using an 'average of surrounding cells' calculation. I've done this in excel but have no idea of how to do it PBI. Any help would be appreciated.

Also i've noticed the 'bins' behaviour is different to what I wanted. e.g. if bin size is 5 then a value of 9 goes into bin 5 and not bin 10. Can this be altered?

Here is the link to the PBI file. https://1drv.ms/u/s!AsRFZB1O95IYd2LHlXpePiUI5A4

Thanks.

parry2k · ‎04-02-2019

@Matt_P i think this will do it, i just used -2 +2 for columns but not rows, in this updated file now it is for both. see attached. sorry for the delay 🙂

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

parry2k · ‎04-10-2019

@Matt_P update smoothing avg total as below and rest everything should work.

Smoothing Avg Total = 
SUMX(
    CROSSJOIN ( 
        ALL( FlatData[ConcSpan (bins)] ), 
        ALL( FlatData[Load (bins)] )
    ) , 
    
    [Smoothing Avg] 
)

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

parry2k · ‎03-30-2019

@Matt_P what calculation you did in excel?? Can you share?

Regarding bins, you can create your own group column if out of the box not working for you.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Matt_P · ‎03-31-2019

@parry2k Thanks for the reply. Here is the link to the excel with the entire dataset. The pbix i supplied has a much smaller dataset. https://1drv.ms/x/s!AsRFZB1O95IYeGGSnykPMX_VMhI

I know I can do custom groups but how do you do a custom bucket? (Currently sorted it out by rounding the data...)

Thanks.

parry2k · ‎03-31-2019

@Matt_P i looked at excel, what is the purpose of dividing by 25 in your average formula. Are we going to use the same calculation giving we have small sample dataset.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Matt_P · ‎03-31-2019

@parry2k no, the small dataset was to try to get a fast answer. I was going to attempt to scale it up...

Below is a link to the pbix file I've created today that represents the data as per the larger dataset in the excel file. DataSmooting2.xlsx (supposed to be smoothing lol). I'm after replicating the 2nd heat map as per the excel. The original data source isn't as neat so it needs to be put into the first matrix, then the averages calculated as per the excel to be put into the 2nd marix. I just have no idea at all of how to get the data out of the 1st matrix...

Sadly I can't share the original data...

https://1drv.ms/u/s!AsRFZB1O95IYedR1LM-SOVIqML4 << pbix file.

Thanks for looking at it!

parry2k · ‎03-31-2019

@Matt_P link is not working, please post again.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Matt_P · ‎03-31-2019

Sorry about that...

https://1drv.ms/u/s!AsRFZB1O95IYebcRftGykJBg5p4

parry2k · ‎03-31-2019

@Matt_P got it, can you also share flatdata excel file used in pbix.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Matt_P · ‎03-31-2019

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

link to flatdata...

Again thanks for looking into this.

parry2k · ‎03-31-2019

@Matt_P does this looks correct to you?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Matt_P · ‎03-31-2019

WOW.

Yes that looks like it, if its not exactly the same its very close. Can you share it?

parry2k · ‎04-01-2019

@Matt_P I might be missing something in the calculation, solution is attached you can tweak as per your need.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Matt_P · ‎04-01-2019

@parry2k Thanks very much for your help!!

Its close, but looking at the numbers the formulae is only taking 1 row and I would like it to take 'the' row, 2 rows above and 2 rows below.

Any assistance with this would be greatly appreciated!

parry2k · ‎04-01-2019

@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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Matt_P · ‎04-01-2019

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 · ‎04-02-2019

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k · ‎04-02-2019

@Matt_P i think this will do it, i just used -2 +2 for columns but not rows, in this updated file now it is for both. see attached. sorry for the delay 🙂

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Matt_P · ‎04-02-2019

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

Matt_P · ‎04-05-2019

@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 · ‎04-05-2019

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.