Forum Discussion

cbruhn42's avatar
cbruhn42
Helper III
2 years ago
Solved

Averaging Last 4 values using an index

I am trying to figure out how to average the last 4 values for a given attribute using an index that I setup by first sorting by the attribute.  So when I filter down to a specific attribute the inde...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi cbruhn42 ,

    I think to use the column 'SampleDatetime'.

    Measure = 
VAR _a = SELECTEDVALUE('Table'[SampleDateTime])
VAR _b = DATE(YEAR(_a),MONTH(_a),DAY(_a))
VAR _startTime = _b + TIME(0,0,0)
VAR _endTime = _b + TIME(23,59,59)
VAR _endIndex =
    CALCULATE (
        MAX ( 'Table'[Attribute Index] ),
        ALLEXCEPT ( 'Table', 'Table'[Comment_ValueNumber], 'Table'[Attribute] ),
        'Table'[SampleDateTime] >= _startTime && 'Table'[SampleDateTime] <= _endTime
    )
VAR _sample =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Comment_ValueNumber], 'Table'[Attribute] )
    ) //Calculate sample size
VAR _startIndex =
    IF ( _sample < 4, _endIndex - _sample + 1, _endIndex - 3 )
RETURN
    CALCULATE (
        AVERAGEX (
            FILTER (
                'Table',
                'Table'[Attribute Index] >= _startIndex
                    && 'Table'[Attribute Index] <= _endIndex
            ),
            [Value]
        ),
        ALLEXCEPT ( 'Table', 'Table'[Comment_ValueNumber], 'Table'[Attribute] )
    )

     

cbruhn42's avatar
cbruhn42
Helper III
2 years ago

This is close, but I failed to mention that there are multiple data setes assigned to each valve number in the data set.  So I need the average of the 4 per group.  It looks like this example for valve #1.

 

 

 

 

Do we need to reference the SampleDatetime column to know how to group the samples together?  Or could we use the Attribute Index and only average the samples that are within 4 in the attribute index column?

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi cbruhn42 ,

I think to use the column 'SampleDatetime'.

Measure = 
VAR _a = SELECTEDVALUE('Table'[SampleDateTime])
VAR _b = DATE(YEAR(_a),MONTH(_a),DAY(_a))
VAR _startTime = _b + TIME(0,0,0)
VAR _endTime = _b + TIME(23,59,59)
VAR _endIndex =
    CALCULATE (
        MAX ( 'Table'[Attribute Index] ),
        ALLEXCEPT ( 'Table', 'Table'[Comment_ValueNumber], 'Table'[Attribute] ),
        'Table'[SampleDateTime] >= _startTime && 'Table'[SampleDateTime] <= _endTime
    )
VAR _sample =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Comment_ValueNumber], 'Table'[Attribute] )
    ) //Calculate sample size
VAR _startIndex =
    IF ( _sample < 4, _endIndex - _sample + 1, _endIndex - 3 )
RETURN
    CALCULATE (
        AVERAGEX (
            FILTER (
                'Table',
                'Table'[Attribute Index] >= _startIndex
                    && 'Table'[Attribute Index] <= _endIndex
            ),
            [Value]
        ),
        ALLEXCEPT ( 'Table', 'Table'[Comment_ValueNumber], 'Table'[Attribute] )
    )