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 😉

Gabry's avatar
Gabry
Super User
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 😉

integrapeter's avatar
integrapeter
Frequent Visitor
4 months ago

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