Forum Discussion

integrapeter's avatar
integrapeter
Frequent Visitor
4 months ago
Solved

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

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 reoco...
average by minute
Large Dataset Problem
Large Datasets
  • Gabry's avatar
    Gabry
    4 months ago

    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 😉

julsr's avatar
julsr
Continued Contributor
4 months ago

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!