Data Smoothing (moving average in matrix)

[Matt_P]

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 · Accepted solution]

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

[parry2k]

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

[parry2k]

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

[Matt_P]

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

[parry2k]

@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] )

[Matt_P]

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

[parry2k]

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

[Matt_P]

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

AB = CALCULATETABLE(FlatData,FlatData[ConcSpan]>=900,FlatData[ConcSpan]<=1500,FlatData[Load (bins)]>=0,FlatData[Load (bins)]<=20).  The total of the table Qty is the answer for this cell

But how would do I get this to iterate over the entire dataset to produce a table of data:

ConcSpan | Load | SmoothingAverage

Any help appreciated.

Link to PowerBI: https://1drv.ms/u/s!AsRFZB1O95IYgQFiElJrevnRIAjJ

link to data: https://1drv.ms/u/s!AsRFZB1O95IYgQFiElJrevnRIAjJ

[parry2k]

Hey sorry I totally missed it. I know this is due, will surely get this to you as soon as I am in front of computer

[parry2k]

@Matt_P try following two measures

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

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

[Matt_P]

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

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

[Matt_P]

@parry2k Hey

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

Again thanks for your help!!

[parry2k]

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

[parry2k · Accepted solution]

@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] 
)

[Matt_P]

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

[Anonymous]

@Matt_P  could you please provide the FlatData.xlsx again?

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

[parry2k]

@Matt_P glad it met the requirement. BRING IT ON 🙂