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 li...
Need Help
  • parry2k's avatar
    parry2k
    7 years ago

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

    • Matt_P's avatar
      Matt_P
      Helper I
      7 years ago

      parry2k no, the small dataset was to try to get a fast answer.  :smileyhappy:  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's avatar
        parry2k
        Super User
        7 years ago

        Matt_P link is not working, please post again.