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

37 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    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. 

    • Matt_P's avatar
      Matt_P
      Helper I
      7 years ago

      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's avatar
        parry2k
        Super User
        7 years ago

        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.