Plotting a Time Weighted Average for a Period

[Graham99]

Hi!

I'm very new to PowerBI.  I have a table of sensor data that is logged over time (voltages, currents, temperatures).  I would like to look at average values for a given time period (day, month, etc).

New data is normally logged once per hour.  The trick is that the data is coming over a wireless network and sometimes connection is lost and data is lost.  So the sample points are not necessarily evenly spaced. You may have one data point at 1pm,2pm and then not have any more until 5pm.  Or sometimes an event will cause an extra sample to be logged in order to capture a change in state. 

Simply averaging the column over a time period (taking the average of each sample in that time period) is not the right approach.  Each check in actually has a time weight associated with it and these weights are not equal.

The PowerBI default behavior when you group by month and show the average seems to be to just take this simple average.  How would I go about including the Time Weight component and plotting the average of those values instead?

Thanks!

[v-jiascu-msft · Accepted solution]

Hi @Graham99,

How to get the Time Weight component? By daily average or daily sum? By hourly sum or hourly average? Please share a sample and the result you want. The pbix file would be great.

If you want to sum them up by day then apply the average. you can try it like this:

Measure =
AVERAGEX (
    SUMMARIZE ( Sales, 'Calender'[Date], "perDay", SUM ( Sales[Quantity] ) ),
    [perDay]
)

Best Regards!

Dale