Forum Discussion

Matt_P's avatar
Matt_P
Helper I
7 years ago
Solved

Data Smoothing (moving average in matrix)

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.

 

 

 

Need Help
  • parry2k's avatar
    parry2k
    7 years ago

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