Average value based on multiply samples per second, Average by minute

[integrapeter]

Hello All,

I have a large data set, that comes from a tank level sensor. The sensor samples that tank level between 5 and 6 times a second. The tank hold 1500gals, and the min and max level is reocorded in gal.

I'm looking to get see trends in tank level across mutly days, weeks, and months. Hoever there is far to much data to be useful.

I'm trying to average the tank level across a minute intervals. my first few attempted to average the level value with an anverageX functions and various filters failed.

I think I will need to create a new table with a date, time and level coulmns, but am unsure how to generate the required reduced data set.

sample data.xlsx in a public one drive.

All insights and help into soliving my probelm would be greatly appriated. 

[v-aatheeque]

Hi @integrapeter 

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.

[Gabry · Accepted solution]

Hey @integrapeter ,

you can create a new table using this dax:

aggregated_table = SUMMARIZE(
    ADDCOLUMNS(
        '003803_Yokogawa_WFI260101_03044',
        "Minute", MINUTE('003803_Yokogawa_WFI260101_03044'[datetime])
    ),
    '003803_Yokogawa_WFI260101_03044'[Date],
    [Minute],
    "AVG", AVERAGE('003803_Yokogawa_WFI260101_03044'[Average])
)

Then you can do the measures / analysis on this table. I attach also the pbix.

Remember that it would be better to do this grouping in power query or even better in the source system (dunno if it's a sql or wich storage engine).

Let me know 😉

[integrapeter]

Hey Garby,

Thanks for the syntax. I will be able to move foward with the soultion you provided. There is an incontutnited with my data, will it jumps move than 70 gals in a minute, which is impossible the system, however I don't think that has anything to do with the soultion you provided.

Thanks for the help

-peter

[julsr]

Hi @integrapeter !

Yes, I would also create a new table and create a relationship using Date table as follows:

Use Power Query to build a minute bucket (timestamp truncated to the minute for each row), then Group By that bucket (and your tank / sensor key if you have more than one) and set the aggregation for level to Average (and optionally Min, Max, Count of samples for data quality). Load that aggregated table into the model and relate it to your Date table on the bucket date; your trend visuals then sit on the minute grain by default, with simple measures such as AVERAGE on the pre-aggregated column if you still want a measure layer.

Let me know if it worked!