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] )
    )

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi cbruhn42 ,

If I understand you correctly, you are trying to calculate the average of these values right?

Use the following DAX expression to create a measure

MEASURE =
VAR _endIndex =
    CALCULATE (
        MAX ( 'Table'[Attribute Index] ),
        ALLEXCEPT ( 'Table', 'Table'[Valve Number], 'Table'[Attribute] )
    )
VAR _sample =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Valve Number], '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'[Valve Number], 'Table'[Attribute] )
    )

Final output


Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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] )
    )